asked
Using a tsql merge in SSIS to copy data between databases
I am trying to do something which I think should be very straightforward, but I can't see how to actually do it. I have two databases which contain the same table with the same structure. I want to import the data from one database into the other, inserting new rows where they don't exist and updating rows that match on id. This needs to be done every evening. I am using SQL Server 2012. I think the best way would be to use an SSIS package with an 'execute sql task' that contains a tsql MERGE statement. I can't see how to actually do the two database connections the process would need. I have looked a lot at books on-line, and although it says it is possible, have found no examples. Any help would be really appreciated. Thanks.
If you would like to use MERGE with two tables from two different databases, then there are only 2 options: 1. The two databases exists on the same instance, then there is no issue and you simply use 3 part names in the MERGE statement to identify those tables. 2. If the databases are on different instance, you will have to create a linked server on one of the instances, which points to the other instance. Then in MERGE use 4 part name for the table on the remote instance. If those tables are on different instances and you do not have linked server or are not able to create the linked server, then there is no way to use the MERGE statement and you will rely on other methods.
15 People are following this question.
