Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Unanswered: IDENTITY_INSERT Error Question

    Table1 Structure
    Col0 identity
    Col1 string

    I get the error "An explicit value for the identity column in table
    'Table1' can only be specified when a column list is
    used and IDENTITY_INSERT is ON." Even when I place "SET IDENTITY_INSERT dbo.Table1 ON" just above the insert it doesn't work. I am confused because I do this all day long at work, but on my SQL Server 2005 Server at home gives me this error. Is it because I am trying to insert data from another database? I'm worried because I don't want to run into this problem at work, but as I stated, I do it all the time.

    Code:
    INSERT INTO dbo.Table1 (Col1)
    	SELECT Col1 FROM Database.dbo.Table2
    SQL Server 2005

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need to specify the identity column when doing an identity insert i.e.
    INSERT INTO dbo.Table1 (Col0, Col1)
    SELECT Col0, Col1 FROM Database.dbo.Table2

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    set identity_insert on
    INSERT INTO dbo.Table1 (Col0, Col1)
    SELECT Col0, Col1 FROM Database.dbo.Table2
    set identity_insert off

    or
    if u don't want to insert the identity value then try like this

    INSERT INTO dbo.Table1 ( Col1)
    SELECT Col1 FROM Database.dbo.Table2

    SET IDENTITY_INSERT
    SQL Tutorials: SQL IDENTITY_INSERT

  4. #4
    Join Date
    Jan 2009
    Posts
    35
    thanks for the responses.


    I just performed a test as I am just now getting some free time.

    Maybe I wasn't paying enough attention, but when I looked at my Table Definition, Col0 wasn't specified as an IDENTITY column. Once I made it an IDENTITY column, the insert worked.

    I haven't made a basic mistake like this in a while .

Posting Permissions

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