DTS import to update current rows -- sql server dts

Ed
6/14/2005 9:46:10 PM

deacdb,
I would like to suggest you import it to another table and use T-SQL to do
the update/insert.

Ed

[quoted text, click to view]

deacdb
6/14/2005 10:55:17 PM

Allan and Ed,

Shucks, no shortcuts I guess.

Two questions:
1. To choose rows in the staging table that already exist in the
target table I could use a predicate such as
NOT IN ( SELECT id FROM target_table)
Any other ideas here? Would you go row by row through a cursor?

2. Once I have these rows, I'd have to manually update every column
one by one, yes?
Any suggestions on how?

thanks much,
-deac

[quoted text, click to view]

Allan Mitchell
6/15/2005 12:00:00 AM

Without doubt the fastest and easiest way is to import to staging table
first. You can then use TSQL to do the manipulation.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

[quoted text, click to view]

David Portas
6/15/2005 1:32:11 AM

1. Use NOT EXISTS (usually performs better than NOT IN) or use an outer
join:

INSERT INTO T (...)
SELECT S. ...
FROM S
LEFT JOIN T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

2. UPDATE with a join. Do the UPDATE before the INSERT so that the
target table is smaller and you don't hit the same rows you just
inserted.

--
David Portas
SQL Server MVP
--

deacdb
6/15/2005 11:07:44 AM

Thanks very much for the form of the INSERT using either NOT EXISTS or the
outer join that you demonstrated.

The UPDATE looks harder - I think I'd need to look at one row at a time,
like with a cursor, to see that S.key is present in T and then construct by
hand the UPDATE statement for each column one by one. Is there an easier
way? If not, would you use a cursor or other way to look at each row?

Thanks very much,
Deac

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : DTS import to update current rows