November 10, 2014 at 11:53 am
Hi all,
I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"?
I am struggling with the code and can't work it out. What am I doing wrong?
Thanks in advance
November 10, 2014 at 11:55 am
If you can't use a linked server you'll need to use OPENROWSET instead.
http://msdn.microsoft.com/en-CA/library/ms190312.aspx
Gerald Britton, Pluralsight courses
November 10, 2014 at 12:50 pm
Does table A have an IDENTITY(1,1) as a PK, by any chance?
If yes, you might be able to do something like this:
1) Set SSIS variable to SELECT Max(ID) from tableB.
2) In your tableA dataflow source, SELECT ... from tableA where ID > @MaxVar (from (1) above)
Alternatively, something similar using a DateCreated column, if such a column exists, in which case you'll have to be a bit more careful when firing data into your destination.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2014 at 2:01 pm
Assuming size and performance allow you can just grab all the records in table B pull them into a temp table on server A, do your comparison insert there then drop the temp table.
November 11, 2014 at 3:40 pm
Thanks guys, The data is in 6 tables with over 50 million rows so I can't bring them all over to one server. The first 2 answers work so I'll check performance now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply