I am using SQL Server 2012's Import wizard to import data from DatabaseA to DatabaseB.
- Source: DatabaseA table A
- Destination: DatabaseB table B
I have written a source query that does the job fine, but I have only 2 options
- Delete destination data - and pull in everything
- Append data - but this creates duplicates (i.e. pulls in everything all over again)
So I wanted to try modify my source query to be something like below
select col1, col2, col3 from A where col1 not in (select col1 from DatabaseB.dbo.B)
In other words I want to test that the data I am pulling from DatabaseA's table A does not already exist Database B's table B where col1 is a unique value.
I would like to be able to do this over and over again, only ever pulling across the delta data
Whenever I try this I get an error stating
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'in'. (Microsoft SQL Server Native Client 11.0)
Some extra things worth noting (might give some clues)
- DatabaseA and DatabaseB are on separate servers
- DatabaseA.A.col1 is an identity column
- DatabaseB.B is a table I created to match the columns of A, it has no identity column, or primary keys etc as it is only a staging table for a migration script I will run after