SQL Server 2008 "IDENTITY_INSERT is ON" error on Insert

Question

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?

Answer 1

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