INSERTING RECORDS FROM A SELECT

• Neil Barker

  Valued Member

  Points: 53

  More actions

  January 19, 2005 at 4:03 pm

  #60914

  So, i'm being really dumb here, and this is a real quick fix.

  I am doing data migration and have 2 tables (catalog and products) in 2 differrent database.  The fields are similar in the 2 tables.  I'm trying to select all the rows in the old table (products) and insert them into the new table (catalog). 

  Both these tables have an identity on the catalogID field.

  =========================================================

  SET IDENTITY_INSERT Catalog OFF

  INSERT INTO Catalog

   SELECT

    catalogID  AS [CatalogID],

    cSubCategory AS [ManID],

    cCode  AS [Code],

    cName  AS [Name], 

    cDescription AS [Description],

    cImageURL AS [Image],

    cSpecials AS [Special],

    cNew  AS [New],

    cWeight  AS [Weight],

    VAT  AS [VAT],

    RRP  AS [RRP],

    0  AS [StackID],

    0  AS [IsHidden],

    1  AS IsEnabled

   FROM 

    old_db..products

  SET IDENTITY_INSERT Catalog ON

  =======================================================

  I'm getting this error: An explicit value for the identity column in table 'Catalog' can only be specified when a column list is used and IDENTITY_INSERT is ON.

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  January 19, 2005 at 9:35 pm

  #538207

  I think that the error message is spot on, just a slight change required:

  INSERT INTO Catalog (fieldname1, fieldname2, ... , fieldnamenn)

  SELECT

  catalogID AS [CatalogID],

  cSubCategory AS [ManID],

  etc

  Regards

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• AKM

  SSCommitted

  Points: 1538

  More actions

  January 21, 2005 at 2:53 am

  #538460

  Also, haven't you got your

  SET IDENTITY_INSERT Catalog OFF

  and

  SET IDENTITY_INSERT Catalog ON

  the wrong way round?

  You want to set it ON before doing the insert and then set it OFF...