Post reply

SET IDENTITY_INSERT dbo.tablename ON is not working

  • April 30, 2007 at 3:04 pm

    #176165

    I need to insert explicit values into the identity column of a table.  I tried using the command SET IDENTITY_INSERT table_name ON, but I still getting cannot copy the data from another table.  Below is the complete script and the error message I got. 

    USE MyDB

    GO

    SET

    IDENTITY_INSERT PrivateDocuments ON

    GO

    INSERT

    into PrivateDocuments

    select

    * from

    Link

    .db.dbo.PrivateDocuments

    GO

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

    What am I doing wrong?  I am a new user of SQL, migrating from Oracle, and I don't have any other resources than the online help and this forum.  

    Thank you in advance,

    Juan

     

  • April 30, 2007 at 3:12 pm

    #703414

    Try something like this and let me know if it works.

    USE MyDB

    GO

    SET IDENTITY_INSERT PrivateDocuments ON

    GO

    Insert into PrivateDocuments(columna,columnb)

    select column1,column2 from

    Link.db.dbo.PrivateDocuments

    GO

     

    Thanks,

    SR

     

     

    Thanks,
    SR

  • April 30, 2007 at 3:18 pm

    #703417

    You must specify the column names as the error message suggests.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • April 30, 2007 at 3:29 pm

    #703426

    I got it!  Thank you for the input to both of you.  I did not now that I have to explicitly list each column of the table. 

    Thanks again.

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply