DTS insert or update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

DTS insert or update

Author

Topic

tholder
Starting Member

3 Posts

Posted - 2002-03-25 : 16:34:28

Hi all,

Here is my problem and it's driving me crazy because I know there must be a simple solution.

I have a tab delimited file which I wish to use as the source for a DTS import. The problem is, I can insert data with DTS but if the record in question exists it does not work - what I want is it to Insert when the record does not exist and update the data when it does.

I know this possible with DTS but how can I do it? Any help or links to relevant information would be greatly apreciated.

All the best
Tom

Jay99

468 Posts

Posted - 2002-03-25 : 16:42:09

Insert the entire file into a temporary staging area and then use a set-based solution to up-sert your table . . .

Jay
<O>

tholder
Starting Member

3 Posts

Posted - 2002-03-25 : 19:29:38

Surely I'll have the same problem though regardless of if the data is in a file or a table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-25 : 20:02:33

No, because you will always be DTS'ing into an empty table. Assuming you have a staging table named "staging", and a primary key column called "ID", the following would work:

DELETE FROM M
FROM mainTable M INNER JOIN staging S ON M.ID=S.ID
INSERT INTO mainTable SELECT * FROM staging

Make sure that you "DELETE FROM staging" before you run the DTS job, then run the above 2 statements, in order, after the DTS job is complete.

tholder
Starting Member

3 Posts

Posted - 2002-03-26 : 05:19:59

Thanks that worked a treat.

Tom

jongregg
Starting Member

31 Posts

Posted - 2002-03-28 : 12:13:53

quote:

No, because you will always be DTS'ing into an empty table. Assuming you have a staging table named "staging", and a primary key column called "ID", the following would work:

DELETE FROM M
FROM mainTable M INNER JOIN staging S ON M.ID=S.ID
INSERT INTO mainTable SELECT * FROM staging

Make sure that you "DELETE FROM staging" before you run the DTS job, then run the above 2 statements, in order, after the DTS job is complete.

Rob, that's one way of doing it but surely the best way is to perform to passes - 1 to update records already in the table, 2 - Insert new records

pass 1

UPDATE
MainTable
SET
...
FROM
Staging S INNER JOIN MainTable M ON
S.ID = M.ID

pass 2

INSERT INTO MainTable
(
..
)
SELECT
...
FROM
Staging S LEFT OUTER JOIN MainTable M ON
S.ID = M.ID
WHERE
M.ID IS NULL

This is the way I've always approached this problem but if there is a better way then I'd love to know about it.

Cheers
Jon

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 12:39:28

That's definitely a better option, unless you have 20 columns and you're not sure which ones were changed!

Whenever I had to do this it was always something like that, so I just wiped out the data and dumped the whole staging table in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources