February 8, 2006 at 10:19 am
I got a flat source database and a sql server target database.
When i want to insert lines from my source to my target, some lines may already exist in the target, and some lines may not.
If they exist, i must update them, and if they not, i must insert them.
So i want to know if there is a data flow transormation or a data flow destination in SQLServer which can do that checking.
Is it possible to do an update between a source and a target without using a data flow transformation?
Thanx
February 8, 2006 at 10:34 am
You can use DDQ (Data Driven Query) task in DTS (SQL 2000). If you do not want to use data flow transformation, then :
a) If the source is a flat file, then you can load the data into a staging table and fire 2 SQLs: One that checks whether the data exists between the source and the destination and then does the update....and for non existant records, does an insert.
b) If the source is a database(could be anything - Oracle, Access, MySQL etc.), you can create a linked server to it (works with text files as well but is very slow)...and then write one update query and one insert query to load the data.
The SQL queries can do any look-ups/transformations that you might need.
February 9, 2006 at 2:10 am
Use method 2 from here: http://www.sqlis.com/default.aspx?311
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 9, 2006 at 2:49 am
Thank you for this method.
This method explain how to insert in destination table datas which aren't present in source table.
Now, what data flow transformation or data flow destination should I use to do an update?
Thanks
February 9, 2006 at 2:53 am
Actually it explains "how to insert in destination table datas which aren't present in destination table"
Use the OLE DB Command transform to do an update.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply