Click here to Skip to main content
15,921,174 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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' .
Posted

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
SQL
sp_addlinkedserver
(you can also do it through SQL Server Management Studio).
e.g:
SQL
EXEC sp_addlinkedserver   @server=N'S1_instance1',  @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'S1\instance1';

You can view if your linked server has been created by querying sys.servers
SQL
select * from sys.servers

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:
SQL
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
 
Share this answer
 
v2
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900