表结构不同时使用Merge在链接服务器和本地服务器之间进行数据同步（SQL）

I have a linked server and Test Db under my localDb Server (SQL 2014).

我在localDb服务器下有一个链接服务器和Test Db（SQL 2014）。

A linked server has a table:

链接服务器有一个表：

Valid State(StateId(char Pk), Name, Desc, CreatedBy, UpdatedBy)

Inside my Test Db I have a table:

在我的测试Db里面我有一张桌子：

Valid State(Id(int PK),Abbreviation(char) ,Name, IsActive)

I need to sync the data between these linked server and my table. What would be the approach to deal with the situation where I can implement SQL- Merge. I got some idea from http://www.sqlservercentral.com/articles/T-SQL/66066/

我需要在这些链接的服务器和我的表之间同步数据。处理我可以实现SQL-Merge的情况的方法是什么。我从http://www.sqlservercentral.com/articles/T-SQL/66066/得到了一些想法

I get to the point where the query works if, the table structures are SAME. But the situation is different where structure are different. Any suggestions will be appreciated :) Thank you !

如果表结构是SAME，我到达查询的作用点。但结构不同的情况则不同。任何建议将不胜感激:)谢谢！

1 个解决方案

#1

Assuming the id's between the systems actually match, this will do what you want:

假设系统之间的id实际匹配，这将做你想要的：

-- Insert any new records
INSERT INTO [Valid State](Id,Name, IsActive)
SELECT StateId, Name 
FROM LinkedServer.database.schema.[Valid State] SRC
WHERE NOT EXISTS (
    SELECT * FROM [Valid State] TGT WHERE TGT.ID = SRC.StateID
    )


-- Update any existing records
UPDATE TGT
SET Name = SRC.Name
FROM [Valid State] TGT
INNER JOIN
LinkedServer.database.schema.[Valid State] SRC
ON SRC.StateID = TGT.ID

Even after all this prompting you haven' explained what you want to do with the leftover fields. So I've left them out

即使在所有这些提示之后，你也“解释了你想要对剩余的领域做些什么。所以我把它们抛弃了

#1