具有不同名称和不同列名称的两个表之间的复制.是否可以创建这样的复制 [英] replication between two tables with different names and which have different column names. Is it possible to create such replication
问题描述
我有一个要求，我要在两个具有不同名称和具有不同列名称的表之间创建复制.是否可以创建这样的复制.
I have a requirement where i have create replication between two tables with different names and which have different column names. Is it possible to create such replication.
server A server B
---------- ----------
Table : Test Table : SUBS
-------------- ---------------
columns A,B,C Columns D,E,F,G,H
我想配置复制，以便将A列数据复制到D列，B列数据复制到E列，C列数据复制到F列
I want to configure replication so that column A data is replicated to column D, column B data is replicated to column E, column C data is replicated to column F
推荐答案
显然，答案是:定义文章时，必须将@vertical_partition参数设置为true，然后添加您要添加的列.想要sp_articlecolumn."
Apparently, the answer is: "When you define the article, you'll have to set the @vertical_partition parameter to true and then add the columns that you want with sp_articlecolumn."
但是，我不得不问你为什么这样做.在我看来，复制并不是在不同数据库之间移动数据的通用工具，而是使两个相同数据库保持同步的工具.
However, I have to ask why you're doing this. Replication in my mind isn't a general tool for moving data around between unlike databases but for keeping two identical databases in sync.
其他集体讨论思路:
- 您可以创建一个与目标表匹配的新源表，并使用触发器使源表保持同步.
- 将源表完整地推到目标位置，然后在目标数据库中进行MERGE.
- 在这里，复制不一定是正确的解决方案.什么是业务规则和要求做到这一点的要求?您是否考虑过使用SSIS?
- 如果需要始终使两个表完全同步，那么对源表(应用程序)进行更改的渠道是什么?听起来您的应用程序需要一个新的抽象层次，一个数据写入层，它知道如何同时写入两个源.
试图保持两个不同数据库之间的数据同步可能是一个问题.竞争条件，缺乏分布式事务(会影响一致性和对故障的响应)，为解决不具有分布式事务而创建的变通办法等可能会存在各种细微的问题.您是否可以创建一个链接服务器和一些视图，这些视图实际上使一个数据库中的数据可以从另一个数据库实时访问?
Trying to keep data synchronized between two different databases can be a problem. There can be all sorts of subtle problems with race conditions, lack of distributed transactions (affecting consistency and response to failures), problems with the workarounds created to deal with not having distributed transactions, and so on and so forth. Can you instead create a linked server and some views that actually make the data in one database real-time accessed from the other?
请向我们详细介绍您的要求以及为什么需要这样做.
Please tell us more about your requirements and why you need to do this.
更新
如果要使用手动更新路线，请注意，您无法同时应用时间段的插入，更新和删除操作.您必须一次顺序一次应用它们.如果改为使用当前状态而不是中间数据操作，则可以一次执行所有行.我将向您展示MERGE示例，而不是历史回放示例.
If you're going the manual update route note that you can't apply a time period's insert, update, and delete operations en masse. You have to apply them one at a time, in order. If you are instead working with current state rather than intermediate data operations, then you can do all rows at once. I will show you the MERGE example, not the history-playback one.
BEGIN TRAN;
DELETE D
FROM LinkedServer.dbo.Dest D WITH (TABLOCKX, HOLDLOCK)
WHERE
NOT EXISTS (
SELECT *
FROM Source S
WHERE D.Key = S.Key
);
UPDATE D
SET
D.Col1 = S.Col4,
D.Col2 = S.Col5,
D.Col3 = S.Col6,
D.Col4 = S.Col7,
FROM
LinkedServer.dbo.Dest D
INNER JOIN Source S ON D.Key = S.Key
WHERE
D.Col1 <> S.Col4
OR EXISTS (
SELECT D.Col2, D.Col4
EXCEPT
SELECT S.Col3, S.Col6
); -- or some other way to handle comparison of nullable columns
INSERT LinkedServer.dbo.Dest (Col1, Col2, Col3)
SELECT Col4, Col5, Col6
FROM Source S WITH (TABLOCK, HOLDLOCK)
WHERE
NOT EXISTS (
SELECT *
FROM LinkedServer.dbo.Dest D
WHERE S.Key = D.Key
);
COMMIT TRAN;
您可能会发现最好推送整个表并在目标服务器上执行合并操作.
You may find it better to push the whole table and do the merge operation on the destination server.
如果要获得一致的时间点快照，我在第一个查询中输入的锁定提示很重要.如果您不在乎，请删除锁定提示.
The lock hints I put in on the first query are important if you're going to have a consistent point-in-time snapshot. If you don't care about that, then take the locking hints out.
如果发现链接服务器上的更新速度很慢，则将整个表一次推送到远程服务器上的临时登台表中，并在脚本中完全在远程服务器上执行MERGE.
If you find that updates across the linked server are slow, then push the entire table in one piece to a temporary staging table on the remote server, and do the MERGE in a script entirely on the remote server.
这篇关于具有不同名称和不同列名称的两个表之间的复制.是否可以创建这样的复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
