How to copy table data from one SQL Server into another SQL server?

Question

How can I copy data from one SQL server data into another. I am trying to copy a table data from server1 to server2. In server1.table1 the data from the table gets added/deleted for an example if I have 10 rows and five more rows gets added, after some time the first ten gets deleted. On the other hand I am copying into an other server, server2.table2. I am using EXCEPT function to copy int data2 is server2. My following code is.

For Server1.

CREATE TABLE database1.dbo.Data1
   (MachineId bigint IDENTITY(1,1) PRIMARY KEY,
   MachineName varchar(50) NOT NULL,
    ProgramName varchar(255) NOT NULL,
   );
GO

For server2.

CREATE TABLE database2.dbo.Data2
       (MachineId bigint IDENTITY(1,1) PRIMARY KEY,
       MachineName varchar(50) NOT NULL,
        ProgramName varchar(255) NOT NULL,
       );
GO

 SET IDENTITY_INSERT [Server2].[database2].[dbo].[Data2] ON 
GO
Insert into [Server2].[database2].[dbo].[Data2] SELECT * from [Server1].[database1].[dbo].[Data1] EXCEPT SELECT * from [Server2].[database2].[dbo].[Data2]

But I get an error:

An explicit value for the identity column in table '[Server2].[database2].[dbo].[Data2]' can only be specified when a column list is used and IDENTITY_INSERT is ON

But I have used it Identity insert.

Answer 1

As the message is clear that you have to define column definition as well to insert value in the identity column explicitly as below. You just need to replace last insert block

INSERT INTO [Server2].[database2].[dbo].[Data2] (MachineId, MachineName, ProgramName)
SELECT * FROM [Server1].[database1].[dbo].[Data1] 
EXCEPT 
SELECT * FROM [Server2].[database2].[dbo].[Data2]

Note: This is the solution for your error message only and I hope rest of the query will work as per your expectation.