Problem with Insert query

[dgillz]

In SQL Query Analyzer, when I try to run an insert query, I am getting the following error:

[red]Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'aab_Split_Complete' can only be specified when a column list is used and IDENTITY_INSERT is ON.[/red]

Any ideas why this is and how I can resolve this?


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JayKusch]

Your table has an identity column on it. If you are using an insert statement, you are going to have to identify what fields you are inserting into and the matching columns they go into. Something like ...

INSERT INTO MyTargetTable (Field1, Field2, Field3 ...)

   SELECT Field1, Field2, Field3 
   FROM   MySourceTable

Now in that example Field1 thru 3 ARE NOT/SHOULD NOT be the field in your insert table that is set as the Identity Field.

You can Check what field is set as your Identity field by going into Enterprise Manager, Drill down to the Database and then the Table level. Then right click on the table you are trying to insert on. In there you should see a field that has setting below the table layout that sets the idenities starting value and how it increments.

Thanks

J. Kusch

 

[EdwinGene]

dgillz,

The error means that you are trying to insert an explicit value into the destination table's identity column. This is usually not allowed because SQL Server keeps track of and inserts the unique values into the column to insure that the values stay unique.

To resolve the issue depends on what you want to do.

If you want the destination table to generate its own identity column values for the newly inserted records, then you are going to have to use an explicit column list (without the identity column) in the 'insert' clause of the insert statement, and have a matching list of values or columns in the 'values'/'select' clause of the insert statement.

If you want to insert your own identity values into the destination table, or retain the identity values from a source table into the destination table, you will need to issue a 'set IDENTITY_INSERT table_name on' command before the insert, and issues a 'set IDENTITY_INSERT table_name off' command after the insert statement. WARNING: If you do this this you need to be very careful that the indentity values you are inserting into the destination table are, and will be, unique in the destination table.