Merge SQL Server databases

Question

I'm attempting to merge multiple SQL Server databases into one. The problem is that one of the tables has an identity field as the primary key. The table also has two uniqueid fields. Something like:

datatable:
id pk identity
link1 uniqueid
link2 uniqueid

My initial thought was to run the following script:

declare @maxId as int
set @maxId = ( SELECT IsNull(MAX(id),0) FROM datatable)
SET IDENTITY_INSERT datatable ON
INSERT INTO database1.datatable id, link1, link2 SELECT id + @maxId, link1, link2 FROM database2.datatable
SET IDENTITY_INSERT datatable OFF

Which works, except that is the query is run multiple times the data gets duplicated. What would be the best way to make sure a combination of link1 and link2 do not already exist in my table?

If it was only one column I could use the IN statement, but unfortunately I need both colums to match.

Answer 1

You mentioned IN so I assume that you want to merge the link columns. This way is safe to run multiple times and avoids the IDENTITY column issue.

INSERT INTO database1.datatable (link1, link2)
SELECT link1, link2
FROM database2.datatable d2
WHERE NOT EXISTS (SELECT *
    FROM
        database1.datatable d1
    WHERE
        d1.link1 = d2.link1 and d1.link2 and d2.link2)

If it's wrong, you can still use EXISTS/NOT EXISTS to deal with multiple column that an IN clause can not...