January 20, 2006 at 12:50 pm
I need to insert data from server a into a table on server b, but only if the table on b is empty. Don't want to link servers and use a sproc with if statement.
DTS with data driven query sounds right but how?
Logic would be if select count(*) from tableB <> 0 then ... but struggling
Any help would be appreciated
January 20, 2006 at 2:27 pm
You could have 1 exec SQL task, doing the Select Count(*) into an output parameter, mapped to a DTS package variable.
Then a 2nd exec SQL task for the insert, where the SQL references an input parameter which is the same DTS variable:
INSERT INTO TableB ...
SELECT ... FROM TableA
WHERE ? = 0
The '?' parameter placeholder gets replaced with the DTS variable, which contains the count from the 1st SQL statement.
January 21, 2006 at 1:55 pm
Thanks.
After a couple of glasses of a nice rioja I braved the DTS. Now I have 1 exec that does the select count(*), another that does the insert and a third that writes back to a table on the first server so that the calling sproc can pass back a custom error message to the application that called it.
Data driven query still sounds interesting though - shame 'help' not very helpful....
January 23, 2006 at 9:56 am
Hi Darren,
Data Driven Query is useful because it can insert, update, and delete from the destination table based on each row of the source data. The drawback is it processes the source data on a row-by-row basis which slows performance of the import.
Greg
Greg
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply