title: Explicit value for the identity column in table error?
body: <p>I get the below error message, I have debugged both identity inserts and they execute with no problems. Has anyone had trouble with this error before and does anyone know how to fix this?</p>
  
  <blockquote>
    <p>Msg 8101, Level 16, State 1, Line 1<br>
    An explicit value for the identity column in table 'c365online_script1.dbo.tProperty' can only be specified when a column list is used and IDENTITY_INSERT is ON.</p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>declare @Source_Database_Name varchar(255) = 'Production2';
  declare @Destination_Database_Name varchar(255) = 'c365online_script1';
  
  declare @Company_Id int = 1 --declare a companyid 
  
  CREATE TABLE #CompanyID (ID bigint)
  
  INSERT INTO #CompanyID(ID)
      VALUES('15')
  
  -- Copy over company records from tCompanytable
  
  --FIRST CURSOR LOOP THROUGH THIS TABLE
  CREATE TABLE #TableList (
      processorder int,
      tablename NVARCHAR(100)
      )
  INSERT INTO #TableList (processorder, tablename )
  VALUES
  (1, 'tProperty');
  
  DECLARE @Counter INT = 0  -- counting variable
  
  ----------- Cursor specific code starts here ------------
  -- company cursor
  declare copyCompanyDataCursor CURSOR fast_forward FOR
  SELECT ID from #CompanyID;
  
  open copyCompanyDataCursor
  fetch next from copyCompanyDataCursor into @Company_Id;
  
  WHILE @@FETCH_STATUS = 0
      BEGIN
          declare @processorder int;
          declare @tablename varchar(500);
          -- table cursor
  
          declare copyTableDataCursor CURSOR fast_forward FOR
          SELECT processorder,tablename from #TableList order by processorder;
  
          open copyTableDataCursor
          fetch next from copyTableDataCursor into @processorder, @tablename;
  
          while @@FETCH_STATUS = 0
          BEGIN
              SET IDENTITY_INSERT [c365online_script1].[dbo].[tCompany] ON
  
              -- Does the table have a companyID column? if statement checking for company id
              IF EXISTS(SELECT * FROM Production2.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='CompanyID' and TABLE_NAME=@tablename)
              BEGIN
                  if @Counter &lt;= 0 -- make sure loop executes only once.
                  BEGIN                   
                  declare @debug varchar(max)             
                  SET @debug = 'INSERT INTO ' + @Destination_Database_Name + '.dbo.' + @tablename + ' SELECT * FROM ' + @Source_Database_Name + '.dbo.' + @tablename + ' WHERE ' + @Source_Database_Name + '.dbo.' + @tablename + '.CompanyID = ' + CAST(@Company_Id as varchar(10))
                  print @debug
                  set @Counter = 1
                  EXEC(@debug)
                  --EXEC('INSERT INTO ' + @Destination_Database_Name + '.dbo.' + @tablename + ' SELECT * FROM ' + @Source_Database_Name + '.dbo.' + @tablename + ' WHERE ' + @Source_Database_Name + '.dbo.' + @tablename + '.CompanyID = ' + @Company_Id )           
                  END             
              END
              ELSE
              BEGIN 
                      Print 'No'
              END 
              -- if yes then copy data based on companyID in cursor  
              -- if no check if this is the first time through company loop and copy all data
              -- if @firstloop company exists look at information schema
  
                      --EXEC('INSERT INTO ' + @Destination_Database_Name + '.dbo.' + @tablename + ' SELECT * FROM ' + @Source_Database_Name + '.dbo.' + @tablename )
                      -- company logic
  
  
              SET IDENTITY_INSERT [c365online_script1].[dbo].[tCompany] OFF
  
              FETCH NEXT FROM copyTableDataCursor into @processorder,@tablename;
          END
  
          close copyTableDataCursor;
  
          Deallocate copyTableDataCursor;
  
  --INSERT INTO c365online_script1.dbo.tCompany
  --SELECT *
  --FROM production2.tCompany
  --WHERE ISNULL(CompanyID, 0) = 0  -- copy all data where id is equal to zero
  --@Destination_Database_Name
  
  --      
          --EXEC(INSERT  + @Destination_Database_Name + '.dbo.' + @tablename + ' SELECT * FROM ' + @Source_Database_Name + '.dbo.' + @tablename + ' WHERE ' + @Source_Database_Name + '.dbo.' + @tablename + '.CompanyID = ' + @Company_Id + ')')     
          --SET @firstLoop = false;
          FETCH NEXT FROM copyCompanyDataCursor into @Company_Id;
      END
  
  CLOSE copyCompanyDataCursor;
  DEALLOCATE copyCompanyDataCursor;
  
  
  --Cleanup
  DROP TABLE #CompanyID
  DROP TABLE #TableList
  </code></pre>