Search code examples
sql-serverlinked-serverdata-transfer

Unable to transfer data between 2 SQL servers on the same network

I am trying to transfer data from one server to another as a one off exercise, I've setup the linked server but I am still having an issue with identity fields

SET IDENTITY_INSERT Regions On
INSERT INTO Regions SELECT * FROM ARACHNE.CMT.dbo.Regions
SET IDENTITY_INSERT Regions Off

Whenever I run the above script, it errors saying

Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'Regions' can only be specified when a column list is used and IDENTITY_INSERT is ON.

But if run SET IDENTITY_INSERT Regions On or SELECT * FROM ARACHNE.CMT.dbo.Regions I get a successful response.

I have checked the permissions of the user on the destination server (which is where I am trying to execute this query) and it does have the right permissions for inserting the data and turning identity_insert on

Solution

  • The error is saying that you have to specify a column list;

    INSERT INTO Regions (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM ARACHNE.CMT.dbo.Regions