How do I Merge two databases?

[T.Taylor]

I have two databases with each one on a different server. I can copy
both databases to the same server with no problems. But how do I merge
them?

This is what I came up with so far but how do I do the same process for
multiple rows and tables?

IF NOT EXISTS (SELECT tmp_DB1.dbo.tb1.key1 FROM tmpDB1.dbo.tb1 WHERE
tmp_DB1.dbo.tb1.key1 = tmp_DB2.dbo.tb1.key1)
INSERT INTO tmp_DB1.dbo.tb1 (<all fields>)
VALUES (<all fields>)
Thanks in advance.

[David Portas]

You probably want to do something like this (insert into a new table only
the rows that don't already exist):

INSERT INTO DB1.dbo.TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM DB2.dbo.SourceTable AS S
LEFT JOIN DB1.dbo.TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

Obviously this won't be useful if Key_col is an IDENTITY column. Use the
natural key of your table(s).

--
David Portas
SQL Server MVP
--

[T.Taylor]

Thanks, it works great. Now, I just have to do this for about 20 more
tables. :)