Post reply

How can I attach some records to a table with existing system-generated index?

  • January 31, 2006 at 11:39 pm

    #91401

    Hello All,

    I am dealing with a CRM client database software. I tried to add 3000 records to a system defined table which already has about more than 10,000 records inside it. But it refused me of this attachment and show me the error below. I think tt's an index problem, can anybody give me a help or hint?

    Server: Msg 8101, Level 16, State 1, Line 1

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

    Then I add one more line of SQL code to it, but it generates the same error.

    My codes is this:

    Set IDENTITY_INSERT DB.dbo.User_Fields_Tbl On

    Insert into DB.dbo.User_Fields_Tbl

    Select * from DB.dbo.MyTable

    The pre-set table User_Fields_Tbl has an index column named "Record_Id", which is the primary key and doesn't allow null. I made all the cells in MyTable's index-matching field as Null at the first time, it refused my append. Then I filled in some numbers in that "Record_Id"-matching column in MyTable, the system table still refused me.

    How can I let that system preset table accept my insertion and also let the system pre-set table automatically generate index following 10,000, such as from 11,000 to 14,000?

    Thanks a lot in advance for your help!

    Henry

  • February 1, 2006 at 12:13 am

    #618049

    >>Set IDENTITY_INSERT DB.dbo.User_Fields_Tbl On

    If you use this SET option, you are telling SQL Server that *you* are going to provide the IDENTITY values. Since your requirement is to "also let the system pre-set table automatically generate", this is not the correct SET option to use.

    >>Insert into DB.dbo.User_Fields_Tbl

    >>Select * from DB.dbo.MyTable

    If you want the system to assign identities, you need to explicitly state the column names in both the INSERT and SELECT statements. You cannot use SELECT * for this. Your INSERT needs to be in this form:

    Insert into DB.dbo.User_Fields_Tbl

      (Column1, Column2 ... ColumnN)

    Select Column1, Column2 ... ColumnN

    From DB.dbo.MyTable

    The column list must exclude the identity column.

     

     

  • February 1, 2006 at 7:10 pm

    #618270

    Thanks a lot indeed PW!

    It's just so easy under your instruction. It's done! I just left out the index column when I inserted those records.

    Cheers,

    Henry

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

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