-1

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

Weiz_ell
  • 3
  • 6
  • Export data using in-built Export and Import data wizard.? – Pranav Bilurkar Jan 26 '15 at 15:32
  • It is a SQL antipattern to use selct *. It is a worse antipattern to use it in an insert. You need to know that the columns in one table match to the correct column in the other table. – HLGEM Jan 26 '15 at 15:36

1 Answers1

4

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
Rhys Jones
  • 5,348
  • 1
  • 23
  • 44
  • 2
    Specifying the explicit columns list is ***ALWAYS*** a good idea anyway! – marc_s Jan 26 '15 at 15:28
  • And skip the id column that is the identity in the list. – HLGEM Jan 26 '15 at 15:35
  • @HLGEM No - the original poster is using `SET IDENTITY_INSERT Regions On` so wants to preserve the identity values. – Rhys Jones Jan 26 '15 at 15:36
  • I disagree, he used that to get a successful run, not becasue he said he needed to preserve the identities but more becasue it sounded as if he was desperately trying anything. If he will always be getting the data from the first table, he should remove the identity value on the desitnation. If he has other records entered directly inthe destination table, then he cannot always successfully preserve the identiities and should not try. – HLGEM Jan 26 '15 at 15:40
  • @HLGEM - Rhys Jones is right. I am trying to preserve the identities. In fact, the destination database is a copy of the target database. However, I set the destination database up a few weeks ago but have not been in the position to move the data, because my customer is still working on it. I need to retain the identities so that the links on foreign keys are retained. – Weiz_ell Jan 26 '15 at 15:44