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.
| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2012-01-09 : 16:49:36
|
| I need to take the results of a query from a connection to a remote sql2008 server (working fine) and update a local server's (sql 2005) database table field based on a key. But, I cannot make a table in the destination database. I can connect, select, etc., in the destination sql server but can't create a table. Thus I can't figure out how to use integration services ssis package to "update" versus create/append, then add a step writing the sql to update. Completely different connections. (It seems that I can create a #temp table, but can't get it to work in the ssis package.) Anybody got an article, blog, etc., that shows how to do this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-09 : 17:26:28
|
| I don't know how to do it with SSIS, but I do know how to do it with a linked server. On the local server, add a linked server that points to the remote server.Then you can use an update statement with a join to the linked server.Here's a quick example:update localset c2 = remote.c2from table1 localjoin [LinkedServerName].DatabaseName..table1 remote on local.c1 = remote.c1where local.c3 = 7Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 05:02:12
|
quote:
Originally posted by janetb
I need to take the results of a query from a connection to a remote sql2008 server (working fine) and update a local server's (sql 2005) database table field based on a key. But, I cannot make a table in the destination database. I can connect, select, etc., in the destination sql server but can't create a table. Thus I can't figure out how to use integration services ssis package to "update" versus create/append, then add a step writing the sql to update. Completely different connections. (It seems that I can create a #temp table, but can't get it to work in the ssis package.) Anybody got an article, blog, etc., that shows how to do this?
you just need a data flow taskthen add a lookup task to your destination table on keyit will have two outputsin match output add a oledb command to do updatein no match output add a oledb destination to do the insert operation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2012-01-10 : 12:26:21
|
| Tara,Thanks a bunch. This worked really well, but it took me a bit to figure out the naming convention for the connection (server,sqlInstance,database,dbo,tablename). Once I got that right, everything was ducky. Much appreciation for taking the time to respond.Janet |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Update local data from remote server data