Identity_insert on in temporary table

I'm having a problem running a stored procedure in SQL Server 2012 and 2008 R2 while it worked fine in my SQL Server 2000 and 2005.

Please see code below:



My existing sp has this code. The purpose is only to copy the structure of TB_Table_2.

Note: TB_Table_2 has an identity column Master_ID.

Next, I will try to insert data with the following code. And this is where I'm having trouble.

WHERE ISNULL(Date,'') = ''
AND ISNULL(ID,'') = ''

Is there something I need to set up in SQL Server to make this work?


the error that I am getting is

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

If your Temp Table has an Identity, to make this insert you need to first:

SET IDENTITY_INSERT #TB_Master_Organization ON

before the insert statement.

You will need to specify the columns to insert:

INSERT INTO #TB_Master_Organization (col1, col2, col3..)
SELECT * FROM TB_Master_Organization
WHERE ISNULL(DeactivatedDate,'') = ''
AND ISNULL(DeactivatedByID,'') = ''

and then, set off the identity_Insert.

So, you got:

SET IDENTITY_INSERT #TB_Master_Organization ON
INSERT INTO #TB_Master_Organization (col1, col2, col3..)
    SELECT * FROM TB_Master_Organization
    WHERE ISNULL(DeactivatedDate,'') = ''
    AND ISNULL(DeactivatedByID,'') = ''

Also, if this not work, take a look at this, maybe can help you

Data insert from temp table to a table with identity insert, The error is: An explicit value for the identity column in table 'actualtable' can only be specified when a column list is used and IDENTITY_INSERT� Allows explicit values to be inserted into the identity column of a table. Transact-SQL Syntax Conventions. Syntax SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF } Arguments. database_name Is the name of the database in which the specified table resides. schema_name Is the name of the schema to which the table belongs.

Here first of all the code you have used to copy the table structure is not correct.As it copies top 1 row also into your new temp table.

To just copy the table structure you can use this below code:

select * INTO #TB_Master_Organization FROM TB_Master_Organization where 1=2

Then you can insert data explicitly by setting the IDENTITY INSERT ON on this temp table.

But You can easily achive this by just by writing this below query:

select * INTO #TB_Master_Organization FROM TB_Master_Organization where WHERE ISNULL(DeactivatedDate,'') = ''
AND ISNULL(DeactivatedByID,'') = ''

Here in one query you will get the final data in your temp table.

Inserting value for identity column in a temporary table , Inserting value for identity column in a temporary table – Learn more on the Now when i want to insert into this table using a Select query, it gives error - values into an IDENTITY column by using SET IDENTITY_INSERT� T-SQL SET Identity_insert. SET Identity_insert - allow to be inserted explicit values into the identity column of a table. The IDENTITY_INSERT statement must be set ON to insert explicit value for identity column. SET Identity_insert Syntax: SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF } ; SET Identity_insert Example:

I have faced the same problem and I have given isnull conversion in the identity column of the table and the issue is fixed ISNULL(P.PersonId,0).

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table , In the outer SP you define a temp table with an Identity field. And in the second SP you do insert to this temp table with. SET IDENTITY_INSERT� DECLARE@country TABLE( CountryId INT IDENTITY(1,1), CountryName VARCHAR(50) NOTnull);INSERTINTO@country ( CountryName )VALUES( 'Scotland'), ( 'England'), ( 'Wales'), ( 'Northern Ireland') ;SELECTCountryId , CountryNameFROM@country ; Truncating a normal table will remove all the rows and reseed the identity value. The following syntax is not valid for a table variable however:

SET IDENTITY_INSERT #TB_Master_Organization ON


You need to select your all columns which you want to insert into a new table and simply run.

It's work for me.

SQL Server – Insert records with value in identity column – Ninja Code, Insert a row into the temp table without a specific value for the identity column. Enable --Set the Identity Insert OFF to you do not inform the Id. Create a temporary table with the identity column. Copy the data from the original table into the temporary table. Drop the original table. Rename the temporary table to the original table name.

Table Variable / Temporary Table (SET IDENTITY_INSERT ON , Find answers to Table Variable / Temporary Table (SET IDENTITY_INSERT ON) - SQL Server from the expert community at Experts Exchange. Below Contain Temp table, i want do send seed value as Dynamically in procedure how to do that. Declare @maxid int Select @maxCfid=max(ID) +1 from Tablename print @maxCfid CREATE TABLE #temptable ( ID int IDENTITY (@maxCfid, 1) NOT NULL , topicid int NOT NULL , Description varchar (250) NULL )

SQL Server Error Messages, The second option is to enable the IDENTITY_INSERT property for the table. If you really want to specify the value for the identity column, this option is the one for� When temp table is used in a different scope and identity insert is performed for the temp table by the SET IDENTITY_INSERT in SQL Server 2019, you may receive an error message that resembles the following:

SQL Server 2019 CTP 2.3 / Issue with scope of set identity_insert on , Note that the temporary table already exists before doing this: I know that local temp tables created inside a stored procedures are dropped when� IDENTITY generates incrementing numbers when a record is inserted into a table. Often referred to as a surrogate key, and commonly used as a primary key to have unique incrementing values in a column. While inserting a record in a table, we do not have to specify identity value explicitly, as by default it takes the next value.

  • SET IDENTITY_INSERT #TB_Master_Organization ON ?
  • I have tried that also but still no luck.
  • What error are you getting? You say it doesn't work - what is it doing?
  • Maybe it wants a column list now as in…
  • What I do not understand though is why you need to create the table first, truncate it and then insert again. Why not lose the first SELECT INTO and just use a single one.