sql server复制表_具有超过246列的表SQL Server复制

最新推荐文章于 2024-07-29 17:17:32 发布

culuo4781

最新推荐文章于 2024-07-29 17:17:32 发布

阅读量214

点赞数

文章标签：触发器数据库 java mysql python

原文链接：https://www.sqlshack.com/sql-server-replication-with-a-table-with-more-than-246-columns/

版权

sql server复制表

问题 (Problem)

In our environment we use SQL Server merge replication to replicate data to multiple subscribers. We had a situation where a table needs to be part of merge replication and the table has more than 246 columns. Merge replication supports tables with maximum of 246 columns. In this article let us see how to replicate a table with more than 246 columns.

在我们的环境中，我们使用SQL Server合并复制将数据复制到多个订户。我们遇到一种情况，其中一个表需要成为合并复制的一部分，并且该表具有246个以上的列。合并复制最多支持246列的表。在本文中，让我们看看如何复制具有246列以上的表。

解 (Solution)

Merge replication is used to replicate data both ways i.e. data is sent from publisher to subscribers and vice versa.

合并复制用于双向复制数据，即数据从发布者发送到订阅者，反之亦然。

In our database we have few tables which has more than 246 columns and they should be part of replication. But, merge replication allows tables with columns not more than 246. So, we created an intermediate table and put all the columns data in the form of xml and replicated intermediate table instead of main table.

在我们的数据库中，只有很少的表具有超过246列，它们应该是复制的一部分。但是，合并复制允许具有不超过246列的表。因此，我们创建了一个中间表，并将所有列数据都以xml的形式放置，并复制了中间表而不是主表。

To explain this, I am creating a sample table “TEST” with more than 246 columns and an intermediate table “TEST_INT”.

为了解释这一点，我将创建一个示例表“ TEST”，其中包含246多个列和一个中间表“ TEST_INT”。

Main table: TEST

主表：TEST

Intermediate table: TEST_INT

中间表：TEST_INT

CREATE TABLE TEST(
ID INT Primary KEY, COL1 INT, COL2 INT, COL3 INT, COL4 INT, COL5 INT, COL6 INT, COL7 INT, COL8 INT,
COL9 INT, COL10 INT, COL11 INT, COL12 INT, COL13 INT, COL14 INT, COL15 INT, COL16 INT, COL17 INT,
COL18 INT, COL19 INT, COL20 INT, COL21 INT, COL22 INT, COL23 INT, COL24 INT, COL25 INT, COL26 INT,
COL27 INT, COL28 INT, COL29 INT, COL30 INT, COL31 INT, COL32 INT, COL33 INT, COL34 INT, COL35 INT,
COL36 INT, COL37 INT, COL38 INT, COL39 INT, COL40 INT, COL41 INT, COL42 INT, COL43 INT, COL44 INT,
COL45 INT, COL46 INT, COL47 INT, COL48 INT, COL49 INT, COL50 INT, COL51 INT, COL52 INT, COL53 INT,
COL54 INT, COL55 INT, COL56 INT, COL57 INT, COL58 INT, COL59 INT, COL60 INT, COL61 INT, COL62 INT,
COL63 INT, COL64 INT, COL65 INT, COL66 INT, COL67 INT, COL68 INT, COL69 INT, COL70 INT, COL71 INT,
COL72 INT, COL73 INT, COL74 INT, COL75 INT, COL76 INT, COL77 INT, COL78 INT, COL79 INT, COL80 INT,
COL81 INT, COL82 INT, COL83 INT, COL84 INT, COL85 INT, COL86 INT, COL87 INT, COL88 INT, COL89 INT,
COL90 INT, COL91 INT, COL92 INT, COL93 INT, COL94 INT, COL95 INT, COL96 INT, COL97 INT, COL98 INT,
COL99 INT, COL100 INT, COL101 INT, COL102 INT, COL103 INT, COL104 INT, COL105 INT, COL106 INT, 
COL107 INT, COL108 INT, COL109 INT, COL110 INT, COL111 INT, COL112 INT, COL113 INT, COL114 INT,
COL115 INT, COL116 INT, COL117 INT, COL118 INT, COL119 INT, COL120 INT, COL121 INT, COL122 INT,
COL123 INT, COL124 INT, COL125 INT, COL126 INT, COL127 INT, COL128 INT, COL129 INT, COL130 INT,
COL131 INT, COL132 INT, COL133 INT, COL134 INT, COL135 INT, COL136 INT, COL137 INT, COL138 INT,
COL139 INT, COL140 INT, COL141 INT, COL142 INT, COL143 INT, COL144 INT, COL145 INT, COL146 INT,
COL147 INT, COL148 INT, COL149 INT, COL150 INT, COL151 INT, COL152 INT, COL153 INT, COL154 INT,
COL155 INT, COL156 INT, COL157 INT, COL158 INT, COL159 INT, COL160 INT, COL161 INT, COL162 INT,
COL163 INT, COL164 INT, COL165 INT, COL166 INT, COL167 INT, COL168 INT, COL169 INT, COL170 INT, 
COL171 INT, COL172 INT, COL173 INT, COL174 INT, COL175 INT, COL176 INT, COL177 INT, COL178 INT,
COL179 INT, COL180 INT, COL181 INT, COL182 INT, COL183 INT, COL184 INT, COL185 INT, COL186 INT,
COL187 INT, COL188 INT, COL189 INT, COL190 INT, COL191 INT, COL192 INT, COL193 INT, COL194 INT,
COL195 INT, COL196 INT, COL197 INT, COL198 INT, COL199 INT, COL200 INT, COL201 INT, COL202 INT,
COL203 INT, COL204 INT, COL205 INT, COL206 INT, COL207 INT, COL208 INT, COL209 INT, COL210 INT,
COL211 INT, COL212 INT, COL213 INT, COL214 INT, COL215 INT, COL216 INT, COL217 INT, COL218 INT,
COL219 INT, COL220 INT, COL221 INT, COL222 INT, COL223 INT, COL224 INT, COL225 INT, COL226 INT,
COL227 INT, COL228 INT, COL229 INT, COL230 INT, COL231 INT, COL232 INT, COL233 INT, COL234 INT,
COL235 INT, COL236 INT, COL237 INT, COL238 INT, COL239 INT, COL240 INT, COL241 INT, COL242 INT,
COL243 INT, COL244 INT, COL245 INT, COL246 INT, COL247 INT, COL248 INT, COL249 INT, COL250 INT,
COL251 INT, COL252 INT, COL253 INT, COL254 INT, COL255 INT, COL256 INT, COL257 INT
 )

Create intermediate table “TEST_INT”.

创建中间表“ TEST_INT”。

CREATE TABLE TEST_INT
(ID INT PRIMARY KEY,
XMLDATA NVARCHAR(MAX),
DATECREATED DATETIME,
DATEUPDATED DATETIME
)

触发条件： (Triggers:)

Now we need to create three triggers on the main table “TEST” which inserts, updates, deletes records on intermediate table “TEST_INT”.

现在，我们需要在主表“ TEST”上创建三个触发器，以插入，更新和删除中间表“ TEST_INT”上的记录。

All these triggers on main table “TEST” should be fired only when user perform DML operations and not replication agent. So, we need to mark these three triggers as “NOT FOR REPLICATION” or we can use sessionproperty(‘replication_agent’) to determine if it is a user operation or replication agent operation.

仅当用户执行DML操作而不是复制代理时，才应触发主表“ TEST”上的所有这些触发器。因此，我们需要将这三个触发器标记为“ NOT FOR REPLICATION”，否则我们可以使用sessionproperty （ 'replication_agent' ）来确定它是用户操作还是复制代理操作。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[INS_TST] ON [dbo].[TEST]
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @isrepl SQL_VARIANT
	-- Return if it is a replication agent
	SELECT @isrepl = sessionproperty('replication_agent')
 
		IF (@isrepl = 1)
		BEGIN
			RETURN;
		END
 
	DECLARE @id INT
 
 
	DECLARE cIdCreated CURSOR
	FOR
	SELECT ID
	FROM INSERTED
 
	OPEN cIdCreated
 
	FETCH NEXT
	FROM cIdCreated
	INTO @id
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO TEST_INT (
			ID
			,XMLDATA
			,DATECREATED
			,DATEUPDATED
			)
		VALUES (
			@id
			,(
				SELECT *
				FROM TEST
				WHERE ID = @id
				FOR XML PATH('Data')
				)
			,getdate()
			,getdate()
			);
 
		FETCH NEXT
		FROM cIdCreated
		INTO @id
	END
 
	CLOSE cIdCreated
 
	DEALLOCATE cIdCreated
END
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 
CREATE TRIGGER [dbo].[UPD_TEST] ON [dbo].[TEST]
FOR UPDATE
NOT FOR REPLICATION
AS
 
BEGIN
SET NOCOUNT ON 
DECLARE @isrepl SQL_VARIANT
	-- Return if it is a replication agent
	SELECT @isrepl = sessionproperty('replication_agent')
 
		IF (@isrepl = 1)
		BEGIN
			RETURN;
		END
 
	DECLARE @id INT
 
	DECLARE cIdUpdated CURSOR 
	FOR
	SELECT ID
	FROM INSERTED;
 
	OPEN cIdUpdated
 
	FETCH NEXT
	FROM cIdUpdated
	INTO @id
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
 
	IF EXISTS(select 1 from TEST_INT WHERE ID =@id)
	BEGIN
		UPDATE TEST_INT
		SET XMLDATA = (
				SELECT *
				FROM TEST
				WHERE ID = @id
				FOR XML PATH('Data')
				)
			,DATEUPDATED = getdate()
		WHERE ID = @id;
 
		FETCH NEXT
		FROM cIdUpdated
		INTO @id
	END
	ELSE 
	BEGIN 
	INSERT INTO TEST_INT (
			ID
			,XMLDATA
			,DATECREATED
			,DATEUPDATED
			)
		VALUES (
			@id
			,(
				SELECT *
				FROM TEST
				WHERE ID = @id
				FOR XML PATH('Data')
				)
			,getdate()
			,getdate()
			);
	END
 
	END
 
	CLOSE cIdUpdated
 
	DEALLOCATE cIdUpdated
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[DEL_TST] ON [dbo].[TEST]
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @isrepl SQL_VARIANT
	-- Return if it is a replication agent
	SELECT @isrepl = sessionproperty('replication_agent')
 
		IF (@isrepl = 1)
		BEGIN
			RETURN;
		END
 
	DECLARE @id INT
 
 
	DECLARE cIdDeleted CURSOR
	FOR
	SELECT ID
	FROM deleted
 
	OPEN cIdDeleted
 
	FETCH NEXT
	FROM cIdDeleted
	INTO @id
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM TEST_INT WHERE ID=@id
		
 
		FETCH NEXT
		FROM cIdDeleted
		INTO @id
	END
 
	CLOSE cIdDeleted
 
	DEALLOCATE cIdDeleted
END
GO

We need create three triggers on intermediate table “TEST_INT” for insert, update and delete which inserts, updates and deletes data back on main table “TEST”. These three triggers on intermediate table should fire only when replication agent performs DML operation not the actual user. So, we can include sessionproperty(‘replication_agent’) in the trigger code to check if it is replication agent or not.

我们需要在中间表“ TEST_INT”上创建三个触发器，以进行插入，更新和删除操作，从而将数据插入，更新和删除回主表“ TEST”上。仅当复制代理执行DML操作而不是实际用户时，才应触发中间表上的这三个触发器。因此，我们可以在触发代码中包含sessionproperty （ 'replication_agent' ），以检查其是否为复制代理。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[DEL_TEST_INT] ON [dbo].[TEST_INT] 
FOR DELETE
AS 
BEGIN 
SET NOCOUNT ON
	DECLARE @isrepl SQL_VARIANT
		-- RETURN IF USER DELETES --
	SELECT @isrepl = sessionproperty('replication_agent')
 
		IF (@isrepl = 0)
		BEGIN
			RETURN;
		END
 
  DECLARE @id int
         
  
 
    DECLARE CIdDeleted CURSOR LOCAL FOR 
        SELECT ID FROM DELETED
    
    OPEN CIdDeleted
      FETCH NEXT FROM CIdDeleted into @id
      WHILE @@FETCH_STATUS = 0
      BEGIN
        
         
            DELETE FROM TEST WHERE ID =@id
       
        FETCH NEXT FROM CIdDeleted into @id
      END
    CLOSE CIdDeleted
    DEALLOCATE CIdDeleted  
  END
 
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[INS_TEST_INT] ON [dbo].[TEST_INT]
FOR INSERT
AS 
BEGIN
SET NOCOUNT ON
 
	DECLARE @ID INT,	@idoc  int, @vXML   NVARCHAR(MAX)
	
  
  	-- RETURN IF USER INSERT DATA --
	declare @isrepl sql_variant
	select @isrepl= sessionproperty('replication_agent')
 
 
		  if (@isrepl=0)
		  begin 
		  RETURN;
		  end
 
   DECLARE CIdInserted CURSOR FOR 
	SELECT	ID,CAST(XMLDATA AS NVARCHAR(MAX))
    FROM INSERTED;
	OPEN CIdInserted  
FETCH NEXT FROM CIdInserted INTO @id,@vXML
WHILE @@FETCH_STATUS = 0  
BEGIN  
    
		
 
		EXEC sp_xml_preparedocument @idoc OUTPUT, @vXML
    
 INSERT INTO TEST(ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26,COL27,COL28,COL29,COL30,COL31,COL32,COL33,COL34,COL35,COL36,COL37,COL38,COL39,COL40,COL41,COL42,COL43,COL44,COL45,COL46,COL47,COL48,COL49,COL50,COL51,COL52,COL53,COL54,COL55,COL56,COL57,COL58,COL59,COL60,COL61,COL62,COL63,COL64,COL65,COL66,COL67,COL68,COL69,COL70,COL71,COL72,COL73,COL74,COL75,COL76,COL77,COL78,COL79,COL80,COL81,COL82,COL83,COL84,COL85,COL86,COL87,COL88,COL89,COL90,COL91,COL92,COL93,COL94,COL95,COL96,COL97,COL98,COL99,COL100,COL101,COL102,COL103,COL104,COL105,COL106,COL107,COL108,COL109,COL110,COL111,COL112,COL113,COL114,COL115,COL116,COL117,COL118,COL119,COL120,COL121,COL122,COL123,COL124,COL125,COL126,COL127,COL128,COL129,COL130,COL131,COL132,COL133,COL134,COL135,COL136,COL137,COL138,COL139,COL140,COL141,COL142,COL143,COL144,COL145,COL146,COL147,COL148,COL149,COL150,COL151,COL152,COL153,COL154,COL155,COL156,COL157,COL158,COL159,COL160,COL161,COL162,COL163,COL164,COL165,COL166,COL167,COL168,COL169,COL170,COL171,COL172,COL173,COL174,COL175,COL176,COL177,COL178,COL179,COL180,COL181,COL182,COL183,COL184,COL185,COL186,COL187,COL188,COL189,COL190,COL191,COL192,COL193,COL194,COL195,COL196,COL197,COL198,COL199,COL200,COL201,COL202,COL203,COL204,COL205,COL206,COL207,COL208,COL209,COL210,COL211,COL212,COL213,COL214,COL215,COL216,COL217,COL218,COL219,COL220,COL221,COL222,COL223,COL224,COL225,COL226,COL227,COL228,COL229,COL230,COL231,COL232,COL233,COL234,COL235,COL236,COL237,COL238,COL239,COL240,COL241,COL242,COL243,COL244,COL245,COL246,COL247,COL248,COL249,COL250,COL251,COL252,COL253,COL254,COL255,COL256,COL257)
		  SELECT ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26,COL27,COL28,COL29,COL30,COL31,COL32,COL33,COL34,COL35,COL36,COL37,COL38,COL39,COL40,COL41,COL42,COL43,COL44,COL45,COL46,COL47,COL48,COL49,COL50,COL51,COL52,COL53,COL54,COL55,COL56,COL57,COL58,COL59,COL60,COL61,COL62,COL63,COL64,COL65,COL66,COL67,COL68,COL69,COL70,COL71,COL72,COL73,COL74,COL75,COL76,COL77,COL78,COL79,COL80,COL81,COL82,COL83,COL84,COL85,COL86,COL87,COL88,COL89,COL90,COL91,COL92,COL93,COL94,COL95,COL96,COL97,COL98,COL99,COL100,COL101,COL102,COL103,COL104,COL105,COL106,COL107,COL108,COL109,COL110,COL111,COL112,COL113,COL114,COL115,COL116,COL117,COL118,COL119,COL120,COL121,COL122,COL123,COL124,COL125,COL126,COL127,COL128,COL129,COL130,COL131,COL132,COL133,COL134,COL135,COL136,COL137,COL138,COL139,COL140,COL141,COL142,COL143,COL144,COL145,COL146,COL147,COL148,COL149,COL150,COL151,COL152,COL153,COL154,COL155,COL156,COL157,COL158,COL159,COL160,COL161,COL162,COL163,COL164,COL165,COL166,COL167,COL168,COL169,COL170,COL171,COL172,COL173,COL174,COL175,COL176,COL177,COL178,COL179,COL180,COL181,COL182,COL183,COL184,COL185,COL186,COL187,COL188,COL189,COL190,COL191,COL192,COL193,COL194,COL195,COL196,COL197,COL198,COL199,COL200,COL201,COL202,COL203,COL204,COL205,COL206,COL207,COL208,COL209,COL210,COL211,COL212,COL213,COL214,COL215,COL216,COL217,COL218,COL219,COL220,COL221,COL222,COL223,COL224,COL225,COL226,COL227,COL228,COL229,COL230,COL231,COL232,COL233,COL234,COL235,COL236,COL237,COL238,COL239,COL240,COL241,COL242,COL243,COL244,COL245,COL246,COL247,COL248,COL249,COL250,COL251,COL252,COL253,COL254,COL255,COL256,COL257
		  FROM OPENXML (@idoc, '/Data', 2)
		  WITH (ID		int,
COL1	int,
COL2	int,
COL3	int,
COL4	int,
COL5	int,
COL6	int,
COL7	int,
COL8	int,
COL9	int,
COL10	int,
COL11	int,
COL12	int,
COL13	int,
COL14	int,
COL15	int,
COL16	int,
COL17	int,
COL18	int,
COL19	int,
COL20	int,
COL21	int,
COL22	int,
COL23	int,
COL24	int,
COL25	int,
COL26	int,
COL27	int,
COL28	int,
COL29	int,
COL30	int,
COL31	int,
COL32	int,
COL33	int,
COL34	int,
COL35	int,
COL36	int,
COL37	int,
COL38	int,
COL39	int,
COL40	int,
COL41	int,
COL42	int,
COL43	int,
COL44	int,
COL45	int,
COL46	int,
COL47	int,
COL48	int,
COL49	int,
COL50	int,
COL51	int,
COL52	int,
COL53	int,
COL54	int,
COL55	int,
COL56	int,
COL57	int,
COL58	int,
COL59	int,
COL60	int,
COL61	int,
COL62	int,
COL63	int,
COL64	int,
COL65	int,
COL66	int,
COL67	int,
COL68	int,
COL69	int,
COL70	int,
COL71	int,
COL72	int,
COL73	int,
COL74	int,
COL75	int,
COL76	int,
COL77	int,
COL78	int,
COL79	int,
COL80	int,
COL81	int,
COL82	int,
COL83	int,
COL84	int,
COL85	int,
COL86	int,
COL87	int,
COL88	int,
COL89	int,
COL90	int,
COL91	int,
COL92	int,
COL93	int,
COL94	int,
COL95	int,
COL96	int,
COL97	int,
COL98	int,
COL99	int,
COL100	int,
COL101	int,
COL102	int,
COL103	int,
COL104	int,
COL105	int,
COL106	int,
COL107	int,
COL108	int,
COL109	int,
COL110	int,
COL111	int,
COL112	int,
COL113	int,
COL114	int,
COL115	int,
COL116	int,
COL117	int,
COL118	int,
COL119	int,
COL120	int,
COL121	int,
COL122	int,
COL123	int,
COL124	int,
COL125	int,
COL126	int,
COL127	int,
COL128	int,
COL129	int,
COL130	int,
COL131	int,
COL132	int,
COL133	int,
COL134	int,
COL135	int,
COL136	int,
COL137	int,
COL138	int,
COL139	int,
COL140	int,
COL141	int,
COL142	int,
COL143	int,
COL144	int,
COL145	int,
COL146	int,
COL147	int,
COL148	int,
COL149	int,
COL150	int,
COL151	int,
COL152	int,
COL153	int,
COL154	int,
COL155	int,
COL156	int,
COL157	int,
COL158	int,
COL159	int,
COL160	int,
COL161	int,
COL162	int,
COL163	int,
COL164	int,
COL165	int,
COL166	int,
COL167	int,
COL168	int,
COL169	int,
COL170	int,
COL171	int,
COL172	int,
COL173	int,
COL174	int,
COL175	int,
COL176	int,
COL177	int,
COL178	int,
COL179	int,
COL180	int,
COL181	int,
COL182	int,
COL183	int,
COL184	int,
COL185	int,
COL186	int,
COL187	int,
COL188	int,
COL189	int,
COL190	int,
COL191	int,
COL192	int,
COL193	int,
COL194	int,
COL195	int,
COL196	int,
COL197	int,
COL198	int,
COL199	int,
COL200	int,
COL201	int,
COL202	int,
COL203	int,
COL204	int,
COL205	int,
COL206	int,
COL207	int,
COL208	int,
COL209	int,
COL210	int,
COL211	int,
COL212	int,
COL213	int,
COL214	int,
COL215	int,
COL216	int,
COL217	int,
COL218	int,
COL219	int,
COL220	int,
COL221	int,
COL222	int,
COL223	int,
COL224	int,
COL225	int,
COL226	int,
COL227	int,
COL228	int,
COL229	int,
COL230	int,
COL231	int,
COL232	int,
COL233	int,
COL234	int,
COL235	int,
COL236	int,
COL237	int,
COL238	int,
COL239	int,
COL240	int,
COL241	int,
COL242	int,
COL243	int,
COL244	int,
COL245	int,
COL246	int,
COL247	int,
COL248	int,
COL249	int,
COL250	int,
COL251	int,
COL252	int,
COL253	int,
COL254	int,
COL255	int,
COL256	int,
COL257	int
				)
    
 
			EXEC sp_xml_removedocument @idoc; 
  
	
 
         
 
     FETCH NEXT FROM CIdInserted INTO  @id,@vXML
END 
CLOSE CIdInserted  
DEALLOCATE CIdInserted 
  
END
 
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[UPD_TEST_INT] ON [dbo].[TEST_INT]
FOR UPDATE
AS 
BEGIN
SET NOCOUNT ON
 
	DECLARE @ID INT,	@idoc  int, @vXML   NVARCHAR(MAX)
	
  
  	-- RETURN IF USER INSERT DATA --
	declare @isrepl sql_variant
	select @isrepl= sessionproperty('replication_agent')
 
 
		  if (@isrepl=0)
		  begin 
		  RETURN;
		  end
 
   DECLARE CIdInserted CURSOR FOR 
	SELECT	ID,CAST(XMLDATA AS NVARCHAR(MAX))
    FROM INSERTED;
	OPEN CIdInserted  
FETCH NEXT FROM CIdInserted INTO @id,@vXML
WHILE @@FETCH_STATUS = 0  
BEGIN  
    
		
 
		EXEC sp_xml_preparedocument @idoc OUTPUT, @vXML
		IF NOT EXISTS (SELECT 1 FROM TEST WHERE ID =@ID)
		BEGIN
		INSERT INTO TEST(ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26,COL27,COL28,COL29,COL30,COL31,COL32,COL33,COL34,COL35,COL36,COL37,COL38,COL39,COL40,COL41,COL42,COL43,COL44,COL45,COL46,COL47,COL48,COL49,COL50,COL51,COL52,COL53,COL54,COL55,COL56,COL57,COL58,COL59,COL60,COL61,COL62,COL63,COL64,COL65,COL66,COL67,COL68,COL69,COL70,COL71,COL72,COL73,COL74,COL75,COL76,COL77,COL78,COL79,COL80,COL81,COL82,COL83,COL84,COL85,COL86,COL87,COL88,COL89,COL90,COL91,COL92,COL93,COL94,COL95,COL96,COL97,COL98,COL99,COL100,COL101,COL102,COL103,COL104,COL105,COL106,COL107,COL108,COL109,COL110,COL111,COL112,COL113,COL114,COL115,COL116,COL117,COL118,COL119,COL120,COL121,COL122,COL123,COL124,COL125,COL126,COL127,COL128,COL129,COL130,COL131,COL132,COL133,COL134,COL135,COL136,COL137,COL138,COL139,COL140,COL141,COL142,COL143,COL144,COL145,COL146,COL147,COL148,COL149,COL150,COL151,COL152,COL153,COL154,COL155,COL156,COL157,COL158,COL159,COL160,COL161,COL162,COL163,COL164,COL165,COL166,COL167,COL168,COL169,COL170,COL171,COL172,COL173,COL174,COL175,COL176,COL177,COL178,COL179,COL180,COL181,COL182,COL183,COL184,COL185,COL186,COL187,COL188,COL189,COL190,COL191,COL192,COL193,COL194,COL195,COL196,COL197,COL198,COL199,COL200,COL201,COL202,COL203,COL204,COL205,COL206,COL207,COL208,COL209,COL210,COL211,COL212,COL213,COL214,COL215,COL216,COL217,COL218,COL219,COL220,COL221,COL222,COL223,COL224,COL225,COL226,COL227,COL228,COL229,COL230,COL231,COL232,COL233,COL234,COL235,COL236,COL237,COL238,COL239,COL240,COL241,COL242,COL243,COL244,COL245,COL246,COL247,COL248,COL249,COL250,COL251,COL252,COL253,COL254,COL255,COL256,COL257)
		  SELECT ID,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26,COL27,COL28,COL29,COL30,COL31,COL32,COL33,COL34,COL35,COL36,COL37,COL38,COL39,COL40,COL41,COL42,COL43,COL44,COL45,COL46,COL47,COL48,COL49,COL50,COL51,COL52,COL53,COL54,COL55,COL56,COL57,COL58,COL59,COL60,COL61,COL62,COL63,COL64,COL65,COL66,COL67,COL68,COL69,COL70,COL71,COL72,COL73,COL74,COL75,COL76,COL77,COL78,COL79,COL80,COL81,COL82,COL83,COL84,COL85,COL86,COL87,COL88,COL89,COL90,COL91,COL92,COL93,COL94,COL95,COL96,COL97,COL98,COL99,COL100,COL101,COL102,COL103,COL104,COL105,COL106,COL107,COL108,COL109,COL110,COL111,COL112,COL113,COL114,COL115,COL116,COL117,COL118,COL119,COL120,COL121,COL122,COL123,COL124,COL125,COL126,COL127,COL128,COL129,COL130,COL131,COL132,COL133,COL134,COL135,COL136,COL137,COL138,COL139,COL140,COL141,COL142,COL143,COL144,COL145,COL146,COL147,COL148,COL149,COL150,COL151,COL152,COL153,COL154,COL155,COL156,COL157,COL158,COL159,COL160,COL161,COL162,COL163,COL164,COL165,COL166,COL167,COL168,COL169,COL170,COL171,COL172,COL173,COL174,COL175,COL176,COL177,COL178,COL179,COL180,COL181,COL182,COL183,COL184,COL185,COL186,COL187,COL188,COL189,COL190,COL191,COL192,COL193,COL194,COL195,COL196,COL197,COL198,COL199,COL200,COL201,COL202,COL203,COL204,COL205,COL206,COL207,COL208,COL209,COL210,COL211,COL212,COL213,COL214,COL215,COL216,COL217,COL218,COL219,COL220,COL221,COL222,COL223,COL224,COL225,COL226,COL227,COL228,COL229,COL230,COL231,COL232,COL233,COL234,COL235,COL236,COL237,COL238,COL239,COL240,COL241,COL242,COL243,COL244,COL245,COL246,COL247,COL248,COL249,COL250,COL251,COL252,COL253,COL254,COL255,COL256,COL257
		  FROM OPENXML (@idoc, '/Data', 2)
		  WITH (ID		int,
COL1	int,
COL2	int,
COL3	int,
COL4	int,
COL5	int,
COL6	int,
COL7	int,
COL8	int,
COL9	int,
COL10	int,
COL11	int,
COL12	int,
COL13	int,
COL14	int,
COL15	int,
COL16	int,
COL17	int,
COL18	int,
COL19	int,
COL20	int,
COL21	int,
COL22	int,
COL23	int,
COL24	int,
COL25	int,
COL26	int,
COL27	int,
COL28	int,
COL29	int,
COL30	int,
COL31	int,
COL32	int,
COL33	int,
COL34	int,
COL35	int,
COL36	int,
COL37	int,
COL38	int,
COL39	int,
COL40	int,
COL41	int,
COL42	int,
COL43	int,
COL44	int,
COL45	int,
COL46	int,
COL47	int,
COL48	int,
COL49	int,
COL50	int,
COL51	int,
COL52	int,
COL53	int,
COL54	int,
COL55	int,
COL56	int,
COL57	int,
COL58	int,
COL59	int,
COL60	int,
COL61	int,
COL62	int,
COL63	int,
COL64	int,
COL65	int,
COL66	int,
COL67	int,
COL68	int,
COL69	int,
COL70	int,
COL71	int,
COL72	int,
COL73	int,
COL74	int,
COL75	int,
COL76	int,
COL77	int,
COL78	int,
COL79	int,
COL80	int,
COL81	int,
COL82	int,
COL83	int,
COL84	int,
COL85	int,
COL86	int,
COL87	int,
COL88	int,
COL89	int,
COL90	int,
COL91	int,
COL92	int,
COL93	int,
COL94	int,
COL95	int,
COL96	int,
COL97	int,
COL98	int,
COL99	int,
COL100	int,
COL101	int,
COL102	int,
COL103	int,
COL104	int,
COL105	int,
COL106	int,
COL107	int,
COL108	int,
COL109	int,
COL110	int,
COL111	int,
COL112	int,
COL113	int,
COL114	int,
COL115	int,
COL116	int,
COL117	int,
COL118	int,
COL119	int,
COL120	int,
COL121	int,
COL122	int,
COL123	int,
COL124	int,
COL125	int,
COL126	int,
COL127	int,
COL128	int,
COL129	int,
COL130	int,
COL131	int,
COL132	int,
COL133	int,
COL134	int,
COL135	int,
COL136	int,
COL137	int,
COL138	int,
COL139	int,
COL140	int,
COL141	int,
COL142	int,
COL143	int,
COL144	int,
COL145	int,
COL146	int,
COL147	int,
COL148	int,
COL149	int,
COL150	int,
COL151	int,
COL152	int,
COL153	int,
COL154	int,
COL155	int,
COL156	int,
COL157	int,
COL158	int,
COL159	int,
COL160	int,
COL161	int,
COL162	int,
COL163	int,
COL164	int,
COL165	int,
COL166	int,
COL167	int,
COL168	int,
COL169	int,
COL170	int,
COL171	int,
COL172	int,
COL173	int,
COL174	int,
COL175	int,
COL176	int,
COL177	int,
COL178	int,
COL179	int,
COL180	int,
COL181	int,
COL182	int,
COL183	int,
COL184	int,
COL185	int,
COL186	int,
COL187	int,
COL188	int,
COL189	int,
COL190	int,
COL191	int,
COL192	int,
COL193	int,
COL194	int,
COL195	int,
COL196	int,
COL197	int,
COL198	int,
COL199	int,
COL200	int,
COL201	int,
COL202	int,
COL203	int,
COL204	int,
COL205	int,
COL206	int,
COL207	int,
COL208	int,
COL209	int,
COL210	int,
COL211	int,
COL212	int,
COL213	int,
COL214	int,
COL215	int,
COL216	int,
COL217	int,
COL218	int,
COL219	int,
COL220	int,
COL221	int,
COL222	int,
COL223	int,
COL224	int,
COL225	int,
COL226	int,
COL227	int,
COL228	int,
COL229	int,
COL230	int,
COL231	int,
COL232	int,
COL233	int,
COL234	int,
COL235	int,
COL236	int,
COL237	int,
COL238	int,
COL239	int,
COL240	int,
COL241	int,
COL242	int,
COL243	int,
COL244	int,
COL245	int,
COL246	int,
COL247	int,
COL248	int,
COL249	int,
COL250	int,
COL251	int,
COL252	int,
COL253	int,
COL254	int,
COL255	int,
COL256	int,
COL257	int
				)
		END
  ELSE
  BEGIN  
update t
set
ID	= D.ID		  ,
COL1	= D.COL1  ,
COL2	= D.COL2  ,
COL3	= D.COL3  ,
COL4	= D.COL4  ,
COL5	= D.COL5  ,
COL6	= D.COL6  ,
COL7	= D.COL7  ,
COL8	= D.COL8  ,
COL9	= D.COL9  ,
COL10	= D.COL10 ,
COL11	= D.COL11 ,
COL12	= D.COL12 ,
COL13	= D.COL13 ,
COL14	= D.COL14 ,
COL15	= D.COL15 ,
COL16	= D.COL16 ,
COL17	= D.COL17 ,
COL18	= D.COL18 ,
COL19	= D.COL19 ,
COL20	= D.COL20 ,
COL21	= D.COL21 ,
COL22	= D.COL22 ,
COL23	= D.COL23 ,
COL24	= D.COL24 ,
COL25	= D.COL25 ,
COL26	= D.COL26 ,
COL27	= D.COL27 ,
COL28	= D.COL28 ,
COL29	= D.COL29 ,
COL30	= D.COL30 ,
COL31	= D.COL31 ,
COL32	= D.COL32 ,
COL33	= D.COL33 ,
COL34	= D.COL34 ,
COL35	= D.COL35 ,
COL36	= D.COL36 ,
COL37	= D.COL37 ,
COL38	= D.COL38 ,
COL39	= D.COL39 ,
COL40	= D.COL40 ,
COL41	= D.COL41 ,
COL42	= D.COL42 ,
COL43	= D.COL43 ,
COL44	= D.COL44 ,
COL45	= D.COL45 ,
COL46	= D.COL46 ,
COL47	= D.COL47 ,
COL48	= D.COL48 ,
COL49	= D.COL49 ,
COL50	= D.COL50 ,
COL51	= D.COL51 ,
COL52	= D.COL52 ,
COL53	= D.COL53 ,
COL54	= D.COL54 ,
COL55	= D.COL55 ,
COL56	= D.COL56 ,
COL57	= D.COL57 ,
COL58	= D.COL58 ,
COL59	= D.COL59 ,
COL60	= D.COL60 ,
COL61	= D.COL61 ,
COL62	= D.COL62 ,
COL63	= D.COL63 ,
COL64	= D.COL64 ,
COL65	= D.COL65 ,
COL66	= D.COL66 ,
COL67	= D.COL67 ,
COL68	= D.COL68 ,
COL69	= D.COL69 ,
COL70	= D.COL70 ,
COL71	= D.COL71 ,
COL72	= D.COL72 ,
COL73	= D.COL73 ,
COL74	= D.COL74 ,
COL75	= D.COL75 ,
COL76	= D.COL76 ,
COL77	= D.COL77 ,
COL78	= D.COL78 ,
COL79	= D.COL79 ,
COL80	= D.COL80 ,
COL81	= D.COL81 ,
COL82	= D.COL82 ,
COL83	= D.COL83 ,
COL84	= D.COL84 ,
COL85	= D.COL85 ,
COL86	= D.COL86 ,
COL87	= D.COL87 ,
COL88	= D.COL88 ,
COL89	= D.COL89 ,
COL90	= D.COL90 ,
COL91	= D.COL91 ,
COL92	= D.COL92 ,
COL93	= D.COL93 ,
COL94	= D.COL94 ,
COL95	= D.COL95 ,
COL96	= D.COL96 ,
COL97	= D.COL97 ,
COL98	= D.COL98 ,
COL99	= D.COL99 ,
COL100	= D.COL100,
COL101	= D.COL101,
COL102	= D.COL102,
COL103	= D.COL103,
COL104	= D.COL104,
COL105	= D.COL105,
COL106	= D.COL106,
COL107	= D.COL107,
COL108	= D.COL108,
COL109	= D.COL109,
COL110	= D.COL110,
COL111	= D.COL111,
COL112	= D.COL112,
COL113	= D.COL113,
COL114	= D.COL114,
COL115	= D.COL115,
COL116	= D.COL116,
COL117	= D.COL117,
COL118	= D.COL118,
COL119	= D.COL119,
COL120	= D.COL120,
COL121	= D.COL121,
COL122	= D.COL122,
COL123	= D.COL123,
COL124	= D.COL124,
COL125	= D.COL125,
COL126	= D.COL126,
COL127	= D.COL127,
COL128	= D.COL128,
COL129	= D.COL129,
COL130	= D.COL130,
COL131	= D.COL131,
COL132	= D.COL132,
COL133	= D.COL133,
COL134	= D.COL134,
COL135	= D.COL135,
COL136	= D.COL136,
COL137	= D.COL137,
COL138	= D.COL138,
COL139	= D.COL139,
COL140	= D.COL140,
COL141	= D.COL141,
COL142	= D.COL142,
COL143	= D.COL143,
COL144	= D.COL144,
COL145	= D.COL145,
COL146	= D.COL146,
COL147	= D.COL147,
COL148	= D.COL148,
COL149	= D.COL149,
COL150	= D.COL150,
COL151	= D.COL151,
COL152	= D.COL152,
COL153	= D.COL153,
COL154	= D.COL154,
COL155	= D.COL155,
COL156	= D.COL156,
COL157	= D.COL157,
COL158	= D.COL158,
COL159	= D.COL159,
COL160	= D.COL160,
COL161	= D.COL161,
COL162	= D.COL162,
COL163	= D.COL163,
COL164	= D.COL164,
COL165	= D.COL165,
COL166	= D.COL166,
COL167	= D.COL167,
COL168	= D.COL168,
COL169	= D.COL169,
COL170	= D.COL170,
COL171	= D.COL171,
COL172	= D.COL172,
COL173	= D.COL173,
COL174	= D.COL174,
COL175	= D.COL175,
COL176	= D.COL176,
COL177	= D.COL177,
COL178	= D.COL178,
COL179	= D.COL179,
COL180	= D.COL180,
COL181	= D.COL181,
COL182	= D.COL182,
COL183	= D.COL183,
COL184	= D.COL184,
COL185	= D.COL185,
COL186	= D.COL186,
COL187	= D.COL187,
COL188	= D.COL188,
COL189	= D.COL189,
COL190	= D.COL190,
COL191	= D.COL191,
COL192	= D.COL192,
COL193	= D.COL193,
COL194	= D.COL194,
COL195	= D.COL195,
COL196	= D.COL196,
COL197	= D.COL197,
COL198	= D.COL198,
COL199	= D.COL199,
COL200	= D.COL200,
COL201	= D.COL201,
COL202	= D.COL202,
COL203	= D.COL203,
COL204	= D.COL204,
COL205	= D.COL205,
COL206	= D.COL206,
COL207	= D.COL207,
COL208	= D.COL208,
COL209	= D.COL209,
COL210	= D.COL210,
COL211	= D.COL211,
COL212	= D.COL212,
COL213	= D.COL213,
COL214	= D.COL214,
COL215	= D.COL215,
COL216	= D.COL216,
COL217	= D.COL217,
COL218	= D.COL218,
COL219	= D.COL219,
COL220	= D.COL220,
COL221	= D.COL221,
COL222	= D.COL222,
COL223	= D.COL223,
COL224	= D.COL224,
COL225	= D.COL225,
COL226	= D.COL226,
COL227	= D.COL227,
COL228	= D.COL228,
COL229	= D.COL229,
COL230	= D.COL230,
COL231	= D.COL231,
COL232	= D.COL232,
COL233	= D.COL233,
COL234	= D.COL234,
COL235	= D.COL235,
COL236	= D.COL236,
COL237	= D.COL237,
COL238	= D.COL238,
COL239	= D.COL239,
COL240	= D.COL240,
COL241	= D.COL241,
COL242	= D.COL242,
COL243	= D.COL243,
COL244	= D.COL244,
COL245	= D.COL245,
COL246	= D.COL246,
COL247	= D.COL247,
COL248	= D.COL248,
COL249	= D.COL249,
COL250	= D.COL250,
COL251	= D.COL251,
COL252	= D.COL252,
COL253	= D.COL253,
COL254	= D.COL254,
COL255	= D.COL255,
COL256	= D.COL256,
COL257	= D.COL257
 
	  
		  FROM OPENXML (@idoc, '/Data', 2)
		  WITH (ID		int,
COL1	int,
COL2	int,
COL3	int,
COL4	int,
COL5	int,
COL6	int,
COL7	int,
COL8	int,
COL9	int,
COL10	int,
COL11	int,
COL12	int,
COL13	int,
COL14	int,
COL15	int,
COL16	int,
COL17	int,
COL18	int,
COL19	int,
COL20	int,
COL21	int,
COL22	int,
COL23	int,
COL24	int,
COL25	int,
COL26	int,
COL27	int,
COL28	int,
COL29	int,
COL30	int,
COL31	int,
COL32	int,
COL33	int,
COL34	int,
COL35	int,
COL36	int,
COL37	int,
COL38	int,
COL39	int,
COL40	int,
COL41	int,
COL42	int,
COL43	int,
COL44	int,
COL45	int,
COL46	int,
COL47	int,
COL48	int,
COL49	int,
COL50	int,
COL51	int,
COL52	int,
COL53	int,
COL54	int,
COL55	int,
COL56	int,
COL57	int,
COL58	int,
COL59	int,
COL60	int,
COL61	int,
COL62	int,
COL63	int,
COL64	int,
COL65	int,
COL66	int,
COL67	int,
COL68	int,
COL69	int,
COL70	int,
COL71	int,
COL72	int,
COL73	int,
COL74	int,
COL75	int,
COL76	int,
COL77	int,
COL78	int,
COL79	int,
COL80	int,
COL81	int,
COL82	int,
COL83	int,
COL84	int,
COL85	int,
COL86	int,
COL87	int,
COL88	int,
COL89	int,
COL90	int,
COL91	int,
COL92	int,
COL93	int,
COL94	int,
COL95	int,
COL96	int,
COL97	int,
COL98	int,
COL99	int,
COL100	int,
COL101	int,
COL102	int,
COL103	int,
COL104	int,
COL105	int,
COL106	int,
COL107	int,
COL108	int,
COL109	int,
COL110	int,
COL111	int,
COL112	int,
COL113	int,
COL114	int,
COL115	int,
COL116	int,
COL117	int,
COL118	int,
COL119	int,
COL120	int,
COL121	int,
COL122	int,
COL123	int,
COL124	int,
COL125	int,
COL126	int,
COL127	int,
COL128	int,
COL129	int,
COL130	int,
COL131	int,
COL132	int,
COL133	int,
COL134	int,
COL135	int,
COL136	int,
COL137	int,
COL138	int,
COL139	int,
COL140	int,
COL141	int,
COL142	int,
COL143	int,
COL144	int,
COL145	int,
COL146	int,
COL147	int,
COL148	int,
COL149	int,
COL150	int,
COL151	int,
COL152	int,
COL153	int,
COL154	int,
COL155	int,
COL156	int,
COL157	int,
COL158	int,
COL159	int,
COL160	int,
COL161	int,
COL162	int,
COL163	int,
COL164	int,
COL165	int,
COL166	int,
COL167	int,
COL168	int,
COL169	int,
COL170	int,
COL171	int,
COL172	int,
COL173	int,
COL174	int,
COL175	int,
COL176	int,
COL177	int,
COL178	int,
COL179	int,
COL180	int,
COL181	int,
COL182	int,
COL183	int,
COL184	int,
COL185	int,
COL186	int,
COL187	int,
COL188	int,
COL189	int,
COL190	int,
COL191	int,
COL192	int,
COL193	int,
COL194	int,
COL195	int,
COL196	int,
COL197	int,
COL198	int,
COL199	int,
COL200	int,
COL201	int,
COL202	int,
COL203	int,
COL204	int,
COL205	int,
COL206	int,
COL207	int,
COL208	int,
COL209	int,
COL210	int,
COL211	int,
COL212	int,
COL213	int,
COL214	int,
COL215	int,
COL216	int,
COL217	int,
COL218	int,
COL219	int,
COL220	int,
COL221	int,
COL222	int,
COL223	int,
COL224	int,
COL225	int,
COL226	int,
COL227	int,
COL228	int,
COL229	int,
COL230	int,
COL231	int,
COL232	int,
COL233	int,
COL234	int,
COL235	int,
COL236	int,
COL237	int,
COL238	int,
COL239	int,
COL240	int,
COL241	int,
COL242	int,
COL243	int,
COL244	int,
COL245	int,
COL246	int,
COL247	int,
COL248	int,
COL249	int,
COL250	int,
COL251	int,
COL252	int,
COL253	int,
COL254	int,
COL255	int,
COL256	int,
COL257	int
				)
				 D inner join TEST T ON D.ID=T.ID
				 WHERE T.ID=@ID
    END
 
			EXEC sp_xml_removedocument @idoc; 
  
	
 
         
 
     FETCH NEXT FROM CIdInserted INTO  @id,@vXML
END 
CLOSE CIdInserted  
DEALLOCATE CIdInserted 
  
END
 
GO

We can have one trigger in place to do the same task but to make it simple I am using three different triggers.

我们可以使用一个触发器来执行相同的任务，但是为了简单起见，我使用了三个不同的触发器。

Basically, the flow will be like below for INSERT, UPDATE and DELETE.

基本上，对于INSERT，UPDATE和DELETE，该流程将类似于以下内容。

INSERT:

插：

User inserts data into main table which fires the trigger to insert the same record in intermediate table
用户将数据插入主表中，从而触发触发器以在中间表中插入同一条记录
The insert on intermediate table will be replicated to subscribers using merge agent
中间表上的插入将使用合并代理复制到订阅服务器
Now at the subscriber, the insert on intermediate table will fire insert trigger to insert data back to main table
现在在订阅服务器上，中间表上的插入将触发插入触发器，以将数据插入回主表

UPDATE:

更新：

User updates data on main table which fires the trigger to update the same record in intermediate table
用户更新主表上的数据，这将触发触发器以更新中间表中的相同记录
The update on intermediate table will be replicated to subscribers using merge agent
中间表的更新将使用合并代理复制到订阅服务器
Now at the subscriber, the update on intermediate table will fire update trigger to update data back to main table
现在在订阅服务器上，中间表上的更新将触发更新触发器，以将数据更新回主表

DELETE:

删除：

User deletes data on main table which fires the trigger to delete the same record in intermediate table
用户删除主表上的数据，这将触发触发器以删除中间表中的相同记录
The delete on intermediate table will be replicated to subscribers using merge agent
中间表上的删除将使用合并代理复制到订阅服务器
Now at the subscriber, the delete on intermediate table will fire delete trigger to delete data on main table
现在在订阅服务器上，中间表上的删除将触发删除触发器以删除主表上的数据

Refer to the below image to understand the flow.

请参考下图以了解流程。

We need make sure the main table is created manually on subscriber otherwise the triggers on intermediate table “TEST_INT” fails.

我们需要确保主表是在订阅服务器上手动创建的，否则中间表“ TEST_INT”上的触发器将失败。

Now we need to replicate intermediate table “TEST_INT” instead of main table “TEST”.

现在我们需要复制中间表“ TEST_INT”而不是主表“ TEST”。

创建合并复制： (Create Merge Replication:)

Before creating merge replication, we need to make sure the main table is created on publisher and subscriber with triggers.

在创建合并复制之前，我们需要确保主表是使用触发器在发布者和订阅者上创建的。

The intermediate table is created on Publisher with triggers.

中间表是使用触发器在Publisher上创建的。

Login to SQL Server from SQL Server management studio. Assuming the distribution is already configured navigate to replication folder-> Local publications -> Right click on new publication. Please refer to the below image.

从SQL Server管理工作室登录到SQL Server。假设已配置分发，请导航至复制文件夹->本地发布->右键单击新发布。请参考下图。

Click on Next-> select the database where you created main table and intermediate table. Click Next and select merge replication.

单击下一步->选择在其中创建主表和中间表的数据库。单击下一步，然后选择合并复制。

Click Next and check 2008 or later -> add TEST_INT and click on article properties ->set properties of all table articles-> make sure copy user triggers is enabled.

单击下一步，然后检查2008或更高版本->添加TEST_INT，然后单击文章属性->设置所有表文章的属性->确保启用了复制用户触发器。

Click Next-> Next-> add filter if you want or you can skip and click Next-> check create snapshot immediately and uncheck schedule the snapshot agent to run at following times.

如果需要，请单击“下一步”->“下一步”->“添加过滤器”，或者可以跳过并单击“下一步”->“立即创建快照”，然后取消选中安排快照代理在以下时间运行。

Click Next and add the credentials you want to use for snapshot agent and connecting publisher -> Next -> Next -> and input publication name and finish.

单击下一步，然后添加要用于快照代理和连接发布者的凭据->下一步->下一步->并输入发布名称和完成。

Right click on publication you just created, launch replication monitor and make sure the snapshot is completed.

右键单击刚创建的发布，启动复制监视器并确保快照已完成。

Once the snapshot is completed we need to add the subscriber to publication.

快照完成后，我们需要将订阅者添加到发布中。

Navigate to publication you just created, right click and click on new subscription -> Next – Next -> select the type of subscription you want (Pull or push) as per your need -> Next-> add the subscriber server and select the subscriber database. Add the logins to run merge agent, connect the publisher, distributor and the subscriber -> Next -> select agent schedule as per your need. In my case I selected run continuously -> Initialize immediately -> select subscription type as per your need -> check create subscriber and click Next -> Finish.

导航到您刚刚创建的发布，右键单击并单击新订阅->下一步-下一步->根据需要选择想要的订阅类型（拉或推）->下一步->添加订阅服务器并选择订阅者数据库。添加登录以运行合并代理，根据需要连接发布者，分发者和订阅者->下一步->选择代理时间表。就我而言，我选择连续运行->立即初始化->根据您的需要选择订阅类型->检查创建订阅者，然后单击下一步->完成。

Once the initial snapshot is applied the intermediate table and the triggers on it are created on subscriber.

一旦应用了初始快照，便在订阅服务器上创建了中间表及其上的触发器。

数据加载： (Data Loading:)

After adding the intermediate table to replication, we need to perform below steps if the main table “TEST” has data already.

将中间表添加到复制后，如果主表“ TEST”已经有数据，则需要执行以下步骤。

We ignore below steps if the main table “TEST” is blank and we are inserting data after adding intermediate table “TEST_INT” to replication and snapshot is applied to subscriber.

如果主表“ TEST”为空白，并且在将中间表“ TEST_INT”添加到复制并将快照应用于订户之后，我们正在插入数据，那么我们将忽略以下步骤。

Now as part of initial data load into intermediate table “TEST_INT” we can update any one column with same value. So that update trigger on main table is fired and data is inserted into intermediate table or you can use any other way to load initial data into intermediate table.

现在，作为将初始数据加载到中间表“ TEST_INT”中的一部分，我们可以更新具有相同值的任何一列。这样就可以触发主表上的更新触发器并将数据插入到中间表中，或者可以使用任何其他方式将初始数据加载到中间表中。

UPDATE TEST SET COL1=COL1

Now these updates populate data into intermediate table and are replicated to subscribers. At subscribers the data is inserted back to main table.

现在，这些更新将数据填充到中间表中，并复制到订户。在订户处，数据被插入回到主表中。

Now we have the same data on main table and intermediate at publisher and subscribers.

现在我们在发布者和订阅者的主表和中间表上具有相同的数据。

Now any incremental inserts, updates and deletes on main table will get replicated to subscribers via intermediate table.

现在，主表上的所有增量插入，更新和删除都将通过中间表复制到订阅服务器。

笔记： (Notes:)

Any changes in table structure like dropping column or adding new column to main table “TEST” needs trigger modification.
表结构的任何更改（例如删除列或向主表“ TEST”中添加新列）都需要触发器修改。
Schema changes to main table “TEST” should be done on both publisher and subscribers manually
对主表“ TEST”的架构更改应同时在发布者和订阅者上完成
Trigger change on main table “TEST” should be done on both publisher and subscribers manually
主表“ TEST”上的触发器更改应同时在发布者和订阅者上完成
Make sure the triggers on main table should fire only on user operation not the replication agent
确保主表上的触发器应仅对用户操作而不是复制代理触发
Make sure the triggers on intermediate table should fire only on replication agent operations not the user
确保仅在复制代理操作上而不在用户上触发中间表上的触发器

In this article we have discussed below points.

在本文中，我们讨论了以下几点。

How to replicate a table with more than 246 columns using intermediate table
如何使用中间表复制多于246列的表
Triggers which should fire on user operation
触发用户操作的触发器
Triggers which should fire on replication agent operation
在复制代理操作上应触发的触发器
Use of sessionproperty(‘replication_agent’)
会话属性的使用（ 'replication_agent' ）

Next Steps

下一步

Use this solution if you need to replicate a table with more than 246 columns
如果您需要复制包含多于246列的表，请使用此解决方案
Add more error handling cases to these triggers or the solution to make it strong
向这些触发器或解决方案添加更多错误处理案例，以使其更强大
Validate your data regularly or create an alert to check counts of main and intermediate tables
定期验证数据或创建警报以检查主表和中间表的计数
You can optimize the trigger code and use your own logic to insert/update/delete
您可以优化触发代码，并使用自己的逻辑来插入/更新/删除

目录 (Table of contents)

SQL Server Replication with a table with more than 246 columns

Foreign key issues while applying a snapshot in SQL Server merge replication

SQL Server Replication (Merge) – What gets replicated and what doesn’t

SQL Server Replication (Merge) – Performance Issues in replicating schema changes

Merge SQL Server replication parameterized row filter issues

Log shipping on a mirrored database

具有超过246列的表SQL Server复制

在SQL Server合并复制中应用快照时出现外键问题

SQL Server复制（合并）–复制什么，什么不复制

SQL Server复制（合并）–复制架构更改中的性能问题

合并SQL Server复制参数化的行筛选器问题

镜像数据库上的日志传送

翻译自: https://www.sqlshack.com/sql-server-replication-with-a-table-with-more-than-246-columns/

sql server复制表

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql server复制表_具有超过246列的表SQL Server复制

sql server复制表问题 (Problem) In our environment we use SQL Server merge replication to replicate data to multiple subscribers. We had a situation where a table needs to be part of merge replication ...
复制链接

扫一扫