Update or Insert -- sql server dts

Mark
9/30/2004 12:43:01 AM

I want to load data from a data csv file into a sql server table. This will
happen on a regualr basis. But everytime we get the csv file it might have
old records t oo. So I want to specify if there is a record in the SQL server
table it should be an update else and insert. How do I go about doing this?
Will DTS take care of this automatically? What is the standard way of doing
this in DTS?

Raymond Lewallen
9/30/2004 10:55:47 AM

I would load the data into a temp table. Hopefully you have a primary key
somewhere. Then I would do something like the following where a is the
primary key:

update MyTable set a = z.a, b=z.b, c=z.c from MyTable, MyTempTable z where
exists(select a from MyTable y where y.a = z.a)

Then to insert:

insert MyTable select z.a,z.b,z.c from MyTempTable z where not exists(select
a from MyTable y where y.a = z.a)

The drop table MyTempTable once you are done with it.

--
Raymond Lewallen
http://rlewallen.blogspot.com

[quoted text, click to view]

sql server dts : Update or Insert