April 24, 2014 at 7:24 pm
I am needing some help copying data from a table on another server back to my server instance. It will run every hour or so, and will only copy records that have changed since last run. SSIS would be easiest but the environment I work in makes it not an option. Thanks 🙂
April 25, 2014 at 2:59 am
If it is only one table, can you try using Transactional Replication to replicate this table data to some other table residing on another instance or wherever you want to...
April 25, 2014 at 5:31 am
If you need to do this as a Stored Procedure, all you have to do is get a Linked Server from the other server, then do a LEFT OUTER JOIN from the table of Origin to the table of Destination, set your WHERE clause to "destination column ID IS NULL" and use that as the source of your INSERT statement.
Or you can do a linked server with a MERGE statement. You can find the syntax of it in Books Online.
April 25, 2014 at 11:52 am
Wouldn't the LEFT OUTER JOIN only work for new records, not changed records ?
I was also thinking replication would work.
April 25, 2014 at 5:56 pm
Thanks Brandie Tarvin. I will give that a try. This has to be a stored proc because I do not have full access to the source server to run SSIS or to set up replication.
April 25, 2014 at 5:59 pm
Thanks for the repley rollercoaster, I am not able to have replication set up because I do not own the server. And the company that owns does not use SQL replication at all. So that is why SP was going to have to be the best option.
April 25, 2014 at 6:05 pm
After thinking about your question, I would need to caputure both new records and any updated records. There is a lastmodified column in the tables I need to copy, is there a way to combine a store proc for both?
Thanks again.
April 28, 2014 at 4:18 am
homebrew01 (4/25/2014)
Wouldn't the LEFT OUTER JOIN only work for new records, not changed records ?
Depends on what WHERE clause filters you use. I've done it before where I join on a few specific columns to verify the records are the same.
"AND" is a wonderful keyword, after all.
April 28, 2014 at 4:19 am
RazorbackDBA (4/25/2014)
After thinking about your question, I would need to caputure both new records and any updated records. There is a lastmodified column in the tables I need to copy, is there a way to combine a store proc for both?
See my previous reply. But it seems to me that the MERGE statement is your best option. It does INSERT, UPDATE, and DELETE (but you can ignore the options you don't want to use).
EDIT: And yes, you can use MERGE in a stored procedure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply