SQL server Data Transfer from One server to another

-->
-->Today morning, I have got an email regarding “How to Copy Data Records from Same Table on Different Servers”.

Here I am illustrating the question; client has two Servers, Server 1 and Server 2. Server 1 has table named Employee and Server 2 has same table named Employee. He wants to copy the data records from one table to duplicate table that are present in two different Servers.

We have tried to fix this problem with the help of SQL Server management studio. SQL server management studio 2005 offers a facility to linked servers under the server objects field. Below are the points that we had tried to fix the issue.

• First of all, you will have to connect to Server 2.
• After connecting, link Server 1 and Server 2.
• For linking Server 1 and Server 2.

1. Open SQL server management studio 2005. 
2. Goto the Server objects field.
3. Now right click on the linked Servers.
4. Add a new linked Server.

• Now run the below queries. 

INSERT INTO Server2.DatabaseName.dbo.Employee

(SELECT * FROM Server1.DatabaseName.dbo.Employee)

Where Server 1 and Server 2 are the name of Servers and Employee is the name table. 

Applied For: SQL Server 2005  

How to Avoid Duplicate Data Records? It may possible; that the table on Server 2 has some duplicates data records. You can avoid this by various methods but identification number (ID) are, date are the best method to do this. Updated queries are given below.

With the reference of identification number:
INSERT INTO Server2.DatabaseName.dbo.Employee 2
(SELECT * FROM Server1.DatabaseName.dbo.Employee 1
WHERE B.ID NOT IN (SELECT ID FROM 1)

With the reference of Date:
INSERT INTO Server2.DatabaseName.dbo.Employee 2
(SELECT * FROM Server1.DatabaseName.dbo.Employee 1
WHERE 2.DATE > (SELECT MAX(DATE) FROM 1)