Results 1 to 4 of 4

Thread: Turning IDENTITY_INSERT ON

  1. #1
    Matt Guest

    Turning IDENTITY_INSERT ON


    Im trying to do an INSERT SELECT statement in the following manner:

    INSERT INTO
    DB1.dbo.TABLE
    SELECT *
    FROM dbo.TABLE1
    dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

    And Im given this error message:

    An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


    So if anyone knows how to turn it on it would be a great help.

    Sincerely,
    Matt

  2. #2
    Kurt Guest

    Turning IDENTITY_INSERT ON (reply)

    Matt,
    First, are you sure you want to insert identity values into your table? If you do you can use the Set Identity_Insert statment.

    You would use it just before you insert statement in the same batch:

    Set Identity_Insert db1.dbo.table1 ON

    Then do your insert.


    ------------
    Matt at 8/22/01 4:40:23 PM


    Im trying to do an INSERT SELECT statement in the following manner:

    INSERT INTO
    DB1.dbo.TABLE
    SELECT *
    FROM dbo.TABLE1
    dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

    And Im given this error message:

    An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


    So if anyone knows how to turn it on it would be a great help.

    Sincerely,
    Matt

  3. #3
    Kurt Guest

    Turning IDENTITY_INSERT ON (reply)

    Matt,
    First, are you sure you want to insert identity values into your table? If you do you can use the Set Identity_Insert statment.

    You would use it just before you insert statement in the same batch:

    Set Identity_Insert db1.dbo.table1 ON

    Then do your insert.


    ------------
    Matt at 8/22/01 4:40:23 PM


    Im trying to do an INSERT SELECT statement in the following manner:

    INSERT INTO
    DB1.dbo.TABLE
    SELECT *
    FROM dbo.TABLE1
    dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

    And Im given this error message:

    An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


    So if anyone knows how to turn it on it would be a great help.

    Sincerely,
    Matt

  4. #4
    Guest

    Turning IDENTITY_INSERT ON (reply)

    You have to accomplish two conditions:

    1) set identity insert to ON:
    SET IDENTITY_INSERT db1.dbo.table1 ON

    2) specify column list, for example:
    INSERT INTO
    DB1.dbo.TABLE (column1, column2, column3)
    SELECT column1, column2, column3
    FROM dbo.TABLE1
    dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

    Marcel

    ------------
    Matt at 8/22/01 4:40:23 PM


    Im trying to do an INSERT SELECT statement in the following manner:

    INSERT INTO
    DB1.dbo.TABLE
    SELECT *
    FROM dbo.TABLE1
    dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

    And Im given this error message:

    An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


    So if anyone knows how to turn it on it would be a great help.

    Sincerely,
    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •