How can I set identity_insert on a tablename passed as a variable

I’m using the below sql queries in my stored procedure to copy the records from existing table to duplicate table and ended up at the below error

Script:

-- Step 4: Temporarily allow identity insert on the new table
SET @sql="SET IDENTITY_INSERT " + @new_table + ' ON'
EXEC sp_executesql @sql

-- Step 5: Copy data (including the existing TOWID) into the new table
SET @sql="INSERT INTO " + @new_table + ' (towid, ' + @column_list + ') SELECT towid, ' + @column_list + ' FROM ' + @tableName
EXEC sp_executesql @sql

Error:

Cannot insert explicit value for identity column in table ‘sometableWithIdentity’ when IDENTITY_INSERT is set to OFF error even though it is ON

Tried almost everything , nothing worked

You need to sign in to view this answers

Related Post