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.

@B3S : any suggestions how can i do it, My table Data1 in server1 gets updated and also some data gets deleted. But i need all the data into my server2, table Data2. So I used **EXCEPT**. But the problem is with Identity column, because in case in my server1,table - Data1, few data gets deleted or when new data gets added up it starts with id -11, for an example. I need to copy the last data which are added into Data1 of my server1 into server2. — user9630935, Jun 08 '18 at 11:00

score 4 · Accepted Answer · answered Jun 08 '18 at 11:16

4

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.

answered Jun 08 '18 at 11:16

Shushil Bohara

5,556
2
15
32

i get the error "Cannot insert explicit value for identity column in table 'Data2' when IDENTITY_INSERT is set to OFF." – user9630935 Jun 08 '18 at 11:24
this line also needed before `SET IDENTITY_INSERT [Server2].[database2].[dbo].[Data2] ON ` and please do `OFF` at last – Shushil Bohara Jun 08 '18 at 11:30
yes it worked. Thank you! But now i have a problem with the server to server copying. Cannot find the object "Server2.database2.dbo.Data2" because it does not exist or you do not have permissions. I checked with the permissions, all looks fine. is it something to do with the syntax, **[Server2].[database2].[dbo].[Data2]** – user9630935 Jun 08 '18 at 11:46
You have to `create linked server` to perform this operation so if you already did it then it must be the permission issue. Try it with the fully privileged user just for testing if still not working then you can ask about this issue in the separate question. For linked server reference https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017 – Shushil Bohara Jun 08 '18 at 12:09
Thank you @Susang. But i am using a third party software for the Sql server database connection, which is **rdbDsService** due to which i cannot control traffic. Now my doubt is do i have to still create a link server. ? – user9630935 Jun 14 '18 at 11:52

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

1 Answers1