Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Inserting UNIQUE data into SQL DB with...?

I would like to add new data to a SQL db.  Specifically,  Add new zip codes to existing zip code table.  I import a text file of the most recent and up to date zip codes into a dataset. I next want to INSERT the New zips into the existing zip sql table, only inserting the newest zip codes ( Zip codes added since the last update).
I have a dataset textread with the complete list of zips.  Can I run a dataAdapter Update with some special SQL Insert statement (How would it look) that only Inserts rows of data where the zipe code and city (composite PK) are unique to the existing db?  or other suggestions?

Thanks

J Michael
Friday, May 02, 2003

Um, if you try to Insert data that would break the Primary Key Constraint the Database will reject it.  Hence only new data would make it.  What you do with the returned errors from the Insert is up to you.

Greg Kellerman
Friday, May 02, 2003

Indeed. Or do away with the DataSet and DataAdpater and just rip through the text file using a StreamReader and issue SqlCommands to the database. Stick a try...catch around it to catch those that violate the unique/primary key.

Duncan Smart
Friday, May 02, 2003

Firing off commands and catching errors sounds pretty nasty.

What about, load the data into a temporary holding table. Then do something like :

INSERT INTO GoodTable (postcode)
SELECT postcode
FROM HoldingTable
WHERE postcode NOT IN (Select postcode from GoodTable)

If you have a lot of rows each time, this will be a whole bunch quicker than firing an insert for every record.

OR...

If you don't have many rows for each import : You could do something like :

IF NOT EXISTS (SELECT 1 FROM GoodTable Where Postcode = 90210)
INSERT INTO GoodTable (postcode) VALUES (90210)

Damian
Friday, May 02, 2003

Indeed. I think the majority of the time will be taken up inserting the data into the database in the first place. To do the bulk insert you could use good old BCP.exe.

Duncan Smart
Saturday, May 03, 2003

*  Recent Topics

*  Fog Creek Home