halifaxdal (10/1/2013) Scott Coleman (10/1/2013)
Here are two versions, column lists removed for clarity. Don't rely on "SELECT *" to work across servers.
-- V1: Copy the entire remote table, keep only what you need
INSERT INTO dbo.table
SELECT * FROM linksrv.db.dbo.table
WHERE id NOT IN (SELECT id FROM dbo.table)
This is what I used to do.
"SELECT id FROM dbo.table" will return 200k records from linked server and the local table will contain more than 200k once the local table gets populated, so I am afraid if the efficiency will become an issue in the future.
Yes at some point that will start to run slow. Compared to the common alternative of figuring out which records to keep and move across the wire, you will likely find that copying the entire table is faster. Since the two datasets are on separate servers.
An alternative would be to create an ssis package to migrate just the appropriate data and that can be done more efficiently in some cases.
But for the sake of figuring out which data to keep and "migrate" to the other server, it usually works best to have all of the data on the same server so the comparisons, joins, and evaluation can be done.
Jason AKA CirqueDeSQLeilI have given a name to my pain...
MCM SQL Server, MVP
Posting Performance Based Questions - Gail Shaw