Sql触发器将数据复制到不同服务器中的另一个表 [英] Sql trigger to copy data to another table in diffrent server

问题描述

你好。



i在名为'raj-pc'的sql server中有一个名为'tbltemp'的表，我有另一个sql server'sqlexpress'，因为我有表名为'tblcopy'。我想在插入tbltemp的'raj-pc'中激活触发器，将数据放入'sqlexpress'的'tblcopy'。

hello.

i have table named 'tbltemp' in sql server named 'raj-pc' and i have another sql server 'sqlexpress' in that i have table named 'tblcopy'. i wanted to fire trigger in 'raj-pc' on insert of 'tbltemp' that put data into 'tblcopy' of 'sqlexpress' .

推荐答案

创建链接服务器 到目标服务器，将数据从一个发送到另一个。

要创建链接服务器，您需要使用系统存储过程

Create "Linked Server" to your target server,to send data from one to the other.
To create a linked server you need to use the system stored procedure

sp_addlinkedserver

（你也可以通过SQL Server Management Studio完成）。

例如：

(you can also do it through SQL Server Management Studio).
e.g:

EXEC sp_addlinkedserver   @server=N'S1_instance1',  @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'S1\instance1';

您可以通过查询sys.servers来查看您的链接服务器是否已创建

You can view if your linked server has been created by querying sys.servers

select * from sys.servers

现在，触发器只更新之前未处理过的数据。有很多方法可以做到这一点。如果源表具有更新日期类型列，则可以基于该列执行此操作。或者，如果您的表具有标识列并且您希望以递增方式复制数据，则可以在表中存储每次复制的最后一个ID，然后在下一次触发运行时，您可以告诉它从该开始id + 1表示行只传输一次。

它可能会影响性能和可靠性。

你可以写这样的查询：

Now for the trigger updating only data which hasn't been handled before. There are many ways you can do this. If your source table has an 'update date' type column, you can do it based on that. Alternatively, if your table has an identity column and you want to copy data incrementally you can store in a table the 'last id' which is copied over each time, then on the next run of the trigger you can tell it to start from that id+1 that way rows are only transferred once.
it might have performance and reliability implications.
you can write query like this:

CREATE TRIGGER urTrigger ON Table AFTER INSERT, UPDATE AS
BEGIN
-- your logic
INSERT INTO Table  values ()
INSERT INTO [AnotherSERVERNAME].[DATABASE NAME].dbo.[Table] values()

希望你现在有一些方向。

谢谢

Hope you will have some direction now.
Thanks

你可以通过链接来做到这一点Sql server。术语是链接Sql Server查询。以下是有关如何查询链接服务器的详细信息。最后，触发器将类似于非链接服务器，只是被修改的表被更改。



链接的Sql Server查询

YOu can do this by linking Sql server. The term is Linked Sql Server Query. Here is the details how you can query a linked server. Finally the Trigger will be similar to non-linked server just the table being refereed is changed.

Linked Sql Server Queries

这篇关于Sql触发器将数据复制到不同服务器中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！