CODEWITHSUNDEEP
.netsql-serversql-server-2008-r2odbc
GowthamanSS
GowthamanSS

Reputation: 1484

Error while inserting row into SQL Server with identity

Let us consider the following connection string which connects to SQL Server using the ODBC driver:

Driver={SQL Server Native Client 10.0};Server=xxx.xx.xx.xx,xxxx;Database=mydata;Uid=xx;Pwd=xxxxxx;

I am executing this command:

SET IDENTITY_INSERT db1.table ON;

INSERT INTO db1.table 
   SELECT * FROM db.table

SET IDENTITY_INSERT db1.table OFF;

I am getting errors:

*ERROR [23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]An explicit value for the identity column in table 'db1.table' can only be specified when a column list is used and IDENTITY_INSERT is ON.*

What I have to do now in order to solve the issue?

Waiting for your responses..

Upvotes: 0

Views: 4030

Answers (2)

4b0
4b0

Reputation: 22323

This error occurs when user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement:

Before your SQL Statement:

SET IDENTITY_INSERT <tablename> ON

--You have to make a column list for your INSERT statement:

INSERT Into tableA ([col1], [col2], [col3], [col4], [col5] ) 
SELECT [col1], [col2], [col3], [col4], [col5] FROM tableB

not like:

 "INSERT Into tableA SELECT ........"

After your SQL Statement :

SET IDENTITY_INSERT <tablename> OFF

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147324

You have to explicitly list the columns to insert into:

INSERT db1.table(IdColumn, Column1, Column2)
SELECT IdColumn, Column1, Column2
FROM db.table

You should specify the column names as standard practice anyway to avoid bugs creeping in (e.g. columns misaligned, new columns added etc). When using IDENTITY_INSERT, this is actually enforced by SQL Server, per the error message.

Upvotes: 2

Related Questions