How to insert data from one server to another server using trigger in SQL?

Step 1: Create table Test in Server1 DB.

           

CREATE TABLE [dbo].[Test]

(

            [EmpID] [int] NULL,

            [EmpName] [varchar](50) NOT NULL

)

Step 2: Create table Test1 in Server 2 DB.

CREATE TABLE [dbo].[Test1]

(

            [EmpID] [int] NULL,

            [EmpName] [varchar](50) NOT NULL

)

Step 3: Create Link Server (Server1 to Server2)

           

            How to create link server:

            a) In Object Explorer

                        Expand Server1 >> Server Object >> Linked Server

                        Right Click on Linked Server and choose “New Linked Server”

                       

                        After choosing below dialog appears:

Enter Linked Server Name in Linked Server Text Box.

Select Provider as “SQL Native Client”

Enter Product Name same as Linked Server Name.

Enter Server 2 IP address in Data Source Text Box.

Enter Server 2 Database name in Catalog Text Box in which you want to save data.

 

b) Now Select Security from left pane of “New Linked Server”.

Choose “Be made using this security context”

Enter User name in Remote Login (SQL Server 2 user name).

Enter Password for SQL Server 2 user name.

Click OK.

Now Linked Server for Server1 to Server2 is created in Object Explorer.

Step 4: Create Trigger on Server1 DB Table as

CREATE TRIGGER [dbo].[Trigger_Name] ON [dbo].[Test]

  

FOR  INSERT, UPDATE

AS

BEGIN

DECLARE @empid as int

DECLARE @name as VARCHAR(50)

select @empid=EmpID, @name=EmpName from inserted

insert into [LinkedServerName].[Server2 DB].dbo.Test1 values (@empid, @name)

END

Now insert records in [Server 1 DB] Test Table. It will automatically insert into [Server 2 DB] Test1 Table.