CODEWITHSUNDEEP
sqlsql-server
Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

IDENTITY_INSERT error while trying to insert data into table

I want to copy data from a table named ActionType inside a database TD_EDD, into another table named ActionType inside another database DsVelocity.

I have written the following query:

INSERT INTO [DsVelocity].[dbo].[ActionType]
       ([ActionTypeID]
       ,[ActionTypeName]
       ,[ActiveStatus])
SELECT [ActionTypeID], [ActionType], [Active/Deactive]
FROM [TD_EDD].[dbo].[ActionType]
GO

Whenever I'm trying to do this, I'm getting the following error:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'ActionType' when IDENTITY_INSERT is set to OFF.

I don't understand what's wrong and why I'm getting this error?

Note that I'm using Microsoft SQL Server 2008 R2.

Upvotes: 0

Views: 4952

Answers (2)

evhen14
evhen14

Reputation: 1927

This means that when you insert data into target table, you will have conflicting ids. Most likely ActionTypeId column

to Fix it use

INSERT INTO [DsVelocity].[dbo].[ActionType]
       ([ActionTypeName]
       ,[ActiveStatus])
SELECT [ActionType], [Active/Deactive]
FROM [TD_EDD].[dbo].[ActionType]
GO

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

Well, lets assume from the message that ActionTypeID is an IDENTITY Column. You cannot isert values into this column as it is auto generate, uless you use IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

So you would have to do something like

SET IDENTITY_INSERT [DsVelocity].[dbo].[ActionType] ON

before the insert.

Upvotes: 0

Related Questions