Merge tables Different Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merge tables Different Servers

Author

Topic

hutty
Starting Member

37 Posts

Posted - 2007-07-13 : 15:23:41

I have two databases on two different servers. They are almost identical in terms of layout. I was using DTS to update the tables in Database A with the latest data from Database B. While the table names are identical, Database A tables could have an additional column that is not in the corresponding Database B. The keys are the same for both. What is the easiest way to update A with the data from B that is not in A already? I was trying to work with this, but it only one table and not close to working. Thanks.

MERGE INTO A
USING B
ON A.ID = B.ID
WHEN MATCHED THEN
UPDATE Parent_ID= B.Parent_ID
WHEN NOT MATCHED THEN
INSERT VALUES(A.Parent_ID, B.Parent_ID)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 16:25:53

[code]MERGE A
USING B ON A.ID = B.ID
WHEN MATCHED THEN
UPDATE SET A.Parent_ID = B.Parent_ID
WHEN NOT MATCHED THEN
INSERT (Col1, Col2)
VALUES (A.Parent_ID, B.Parent_ID)[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 16:35:14

UPDATE A
SET A.Parent_ID = B.Parent_ID
FROM A
INNER JOIN B ON B.ID = A.ID

INSERT A (Col1, Col2)
SELECT A.Parent_ID, B.Parent_ID
FROM A INNER JOIN B ON B.ID = A.ID
WHERE B.ID IS NULL

Peter Larsson
Helsingborg, Sweden

hutty
Starting Member

37 Posts

Posted - 2007-07-13 : 18:16:29

Thanks for the response PESO. So that I understand the code. There are 15 columns in B and 16 columns in A.
Would I list all 15 columns in the INSERT command (col1, col2,etc.)?

Should my Select statement include all columns from both tables?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-14 : 03:11:27

quote:
Originally posted by hutty

Thanks for the response PESO. So that I understand the code. There are 15 columns in B and 16 columns in A.
Would I list all 15 columns in the INSERT command (col1, col2,etc.)?

Should my Select statement include all columns from both tables?

Yes if you want to add data to 15 columns
The select statement should select 15 columns whichever are required

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-14 : 03:41:55

Are you using SQL Server 2008 or SQL Server 2005? Becuse MERGE is available in SQL Server 2008.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources