title: SQL Server 2008 "IDENTITY_INSERT is ON" error on Insert
body: <p>I have <code>Locale_Code</code> table in two different databases <code>CP</code> and <code>PP</code></p>
  
  <p>here is the insert script of my table</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Am trying to insert all rows from PP.dbo.LOCALE_CODE  to CP.dbo.LOCALE_CODE as below</p>
  
  <pre><code>SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON
  
  insert into CP.[dbo].[LOCALE_CODE] select * from PP.dbo.LOCALE_CODE  
  </code></pre>
  
  <p>But am getting below error</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>I even tried bulk insert as below</p>
  
  <pre><code>BULK INSERT CP.[dbo].[LOCALE_CODE] from 'C:\locales.csv'
  with (fieldterminator = ',', rowterminator = '\n' )
  go
  </code></pre>
  
  <p>But am getting below error</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>Can someone please help to fix wither BULK insert or direct insert from PP.Locale_Code to PP.Locale_Code?</p>