copy data from Table A to Table B

• geoff hopkins

  Ten Centuries

  Points: 1147

  More actions

  October 21, 2005 at 7:45 am

  #91584

  using MSDE so can't use DTS package builder

  I need to copy data from TableA on database X to TableB on database Y

  i have set the IDENTITY_INSERT to ON

  but the query pane does not like the following so what are you supposed to do? ... probably an easy answer...

  SET IDENTITY_INSERT ON

  INSERT INTO Y.dbo.TableB SELECT * FROM X.dbo.TableA

  SET IDENTITY_INSERT OFF

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  October 21, 2005 at 7:55 am

  #599114

  You're not allowed to use 'select *' with identity_insert, you must explicitly list all columns involved.

  insert newTable (identcol, othercol)

  select identcol, othercol from oldTable

   

  /Kenneth

• geoff hopkins

  Ten Centuries

  Points: 1147

  More actions

  October 21, 2005 at 8:10 am

  #599129

  thanks, spot on!