CODEWITHSUNDEEP
sqlsql-serversql-server-2008
OTUser
OTUser

Reputation: 3848

SQL Server 2008 "IDENTITY_INSERT is ON" error on Insert

I have Locale_Code table in two different databases CP and PP

here is the insert script of my table

CREATE TABLE [dbo].[LOCALE_CODE](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
[active] [bit] NOT NULL,
[code] [nvarchar](4000) NULL,
[created_at] [datetime] NULL,
[created_by] [nvarchar](4000) NULL,
[display_name] [nvarchar](4000) NULL,
[updated_at] [datetime] NULL,
[updated_by] [nvarchar](4000) NULL,
[read_permission] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Am trying to insert all rows from PP.dbo.LOCALE_CODE to CP.dbo.LOCALE_CODE as below

SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON

insert into CP.[dbo].[LOCALE_CODE] select * from PP.dbo.LOCALE_CODE

But am getting below error

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

I even tried bulk insert as below

BULK INSERT CP.[dbo].[LOCALE_CODE] from 'C:\locales.csv'
with (fieldterminator = ',', rowterminator = '\n' )
go

But am getting below error

Explicit value must be specified for identity column in table 'LOCALE_CODE' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Can someone please help to fix wither BULK insert or direct insert from PP.Locale_Code to PP.Locale_Code?

Upvotes: 6

Views: 27545

Answers (2)

Supercoder
Supercoder

Reputation: 1204

There are basically 2 different ways to INSERT records without having an error:

1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT

As per the following example from the same Table created with an IDENTITY PRIMARY KEY:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT from the "INSERT INTO" Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error "Cannot insert explicit value for identify column in table..."

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY "ID" MUST BE PRESENT from the "INSERT INTO" Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error "Explicit value must be specified for identity column table..."

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You need to explicitly mention column names in target table while inserting . Replace Col1,Col2 etc with column list of CP.[dbo].[LOCALE_CODE] table

SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON

insert into CP.[dbo].[LOCALE_CODE] (Col1 ,Col2 , Col3) 
select * from PP.dbo.LOCALE_CODE

You can use * in SELECT of source table PP.dbo.LOCALE_CODE

Upvotes: 9

Related Questions