title: Cannot insert explicit value because IDENTITY_INSERT is OFF, but cannot SET IDENTITY_INSERT to ON because it is already ON
body: <p>I have a table in database <code>Foo</code> named <code>Bar</code>, that has a column named <code>ID</code>, which is the primary key, and this database is living on the development SQL Server.</p>
  
  <p>I'm trying to copy data from our production server into the development server so I can play with said data, so I execute the following:</p>
  
  <pre><code>set IDENTITY_INSERT Foo.dbo.Bar ON
  insert into Foo.dbo.Bar
  (
     ID
     ,Something
     ,Else
     ,Is
     ,Going
     ,Horribly
     ,Wrong
     ,With
     ,SQL
  )
  select 
    ID
    ,Something
    ,Else
    ,Is
    ,Going
    ,Horribly
    ,Wrong
    ,With
    ,SQL
  from Production.Foo.dbo.Bar
  
  set IDENTITY_INSERT Foo.dbo.Bar OFF
  </code></pre>
  
  <p>And I get the error</p>
  
  <blockquote>
    <p>Msg 8107, Level 16, State 1, Line 1
    <br>IDENTITY_INSERT is already ON for table 'Foo.dbo.Bar'. Cannot perform SET operation for table 'Foo.dbo.Bar'.</p>
  </blockquote>
  
  <p>Hmm..okay, so IDENTITY_INSERT is turned on for the table.  So I removed the <code>SET IDENTITY_INSERT Foo.dbo.Bar ON</code> from the top of my query, and then execute it, and I get this error:</p>
  
  <blockquote>
    <p>Msg 544, Level 16, State 1, Line 1
    <br>Cannot insert explicit value for identity column in table 'Bar' when IDENTITY_INSERT is set to OFF.</p>
  </blockquote>
  
  <p>I can execute <code>SET IDENTITY_INSERT Foo.dbo.Bar OFF</code> all day long, but if I try to turn it <code>ON</code>, then SQL Server 2012 says that <code>IDENTITY_INSERT</code> is already turned on.</p>