Linked server and identity_insert

• Uripedes Pants

  Hall of Fame

  Points: 3701

  More actions

  September 28, 2011 at 9:48 am

  #243796

  hi all -

  I am trying to move some data from test to prod. On my test server I have the prod server as a linked server.

  INSERT INTO [prodSERVER\F].[DB].[dbo].

  ([ReportID]

  ,[ReportName]

  ,[ReportDesc])

  SELECT [ReportID]

  ,[ReportName]

  ,[ReportDesc]

  FROM [TESTSERVER].[SCHEMA].

  Several of the tables in testserver have identity columns. I'm trying to use set IDENTITY_INSERT [prodSERVER\F].[DB].[dbo].

  ON

  --GO

  as the first step in the data transfer for each table, then flipping it back off before doing the next, etc. It appears that you can't use that when going through a linked server? Is that correct or am I missing something?

  thanks

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  September 28, 2011 at 9:51 am

  #1387880

  You'd have to switch it around:

  --Run on prod server

  set IDENTITY_INSERT [dbo].

  ON

  INSERT INTO [dbo].

  ([ReportID]

  ,[ReportName]

  ,[ReportDesc])

  SELECT [ReportID]

  ,[ReportName]

  ,[ReportDesc]

  FROM [TESTSERVER].[db].[SCHEMA].

  And just add the linked server from your prod box to the test server.

• Uripedes Pants

  Hall of Fame

  Points: 3701

  More actions

  September 28, 2011 at 10:07 am

  #1387906

  yep, that's what I was afraid of... expensive lesson learned.

  thanks