
Question:
I want to be able to insert data from a table with an identity column into a temporary table in SQL Server 2005.
The TSQL looks something like:
-- Create empty temp table SELECT * INTO #Tmp_MyTable FROM MyTable WHERE 1=0 ... WHILE ... BEGIN ... INSERT INTO #Tmp_MyTable SELECT TOP (@n) * FROM MyTable ... END
The above code created #Tmp_Table with an identity column, and the insert subsequently fails with an error "An explicit value for the identity column in table '#Tmp_MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."
Is there a way in TSQL to drop the identity property of the column in the temporary table without listing all the columns explicitly? I specifically want to use "SELECT *" so that the code will continue to work if new columns are added to MyTable.
I believe dropping and recreating the column will change its position, making it impossible to use SELECT *.
Update:
I've tried using IDENTITY_INSERT as suggested in one response. It's not working - see the repro below. What am I doing wrong?
-- Create test table CREATE TABLE [dbo].[TestTable]( [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) GO -- Insert some data INSERT INTO TestTable (Name) SELECT 'One' UNION ALL SELECT 'Two' UNION ALL SELECT 'Three' GO -- Create empty temp table SELECT * INTO #Tmp FROM TestTable WHERE 1=0 SET IDENTITY_INSERT #Tmp ON -- I also tried OFF / ON INSERT INTO #Tmp SELECT TOP 1 * FROM TestTable SET IDENTITY_INSERT #Tmp OFF GO -- Drop test table DROP TABLE [dbo].[TestTable] GO
Note that the error message "An explicit value for the identity column in table '#TmpMyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON." - I specifically don't want to use a column list as explained above.
Update 2 Tried the suggestion from Mike but this gave the same error:
-- Create empty temp table SELECT * INTO #Tmp FROM (SELECT m1.* FROM TestTable m1 LEFT OUTER JOIN TestTable m2 ON m1.ID=m2.ID WHERE 1=0 ) dt INSERT INTO #Tmp SELECT TOP 1 * FROM TestTable
As for why I want to do this: MyTable is a staging table which can contain a large number of rows to be merged into another table. I want to process the rows from the staging table, insert/update my main table, and delete them from the staging table in a loop that processes N rows per transaction. I realize there are other ways to achieve this.
Update 3
I couldn't get Mike's solution to work, however it suggested the following solution which does work: prefix with a non-identity column and drop the identity column:
SELECT CAST(1 AS NUMERIC(18,0)) AS ID2, * INTO #Tmp FROM TestTable WHERE 1=0 ALTER TABLE #Tmp DROP COLUMN ID INSERT INTO #Tmp SELECT TOP 1 * FROM TestTable
Mike's suggestion to store only the keys in the temporary table is also a good one, though in this specific case there are reasons I prefer to have all columns in the temporary table.
Solution:1
IF you are just processing rows as you describe, wouldn't it be better to just select the top N primary key values into a temp table like:
CREATE TABLE #KeysToProcess ( TempID int not null primary key identity(1,1) ,YourKey1 int not null ,YourKey2 int not null ) INSERT INTO #KeysToProcess (YourKey1,YourKey2) SELECT TOP n YourKey1,YourKey2 FROM MyTable
The keys should not change very often (I hope) but other columns can with no harm to doing it this way.
get the @@ROWCOUNT of the insert and you can do a easy loop on TempID where it will be from 1 to @@ROWCOUNT
and/or
just join #KeysToProcess to your MyKeys table and be on your way, with no need to duplicate all the data.
This runs fine on my SQL Server 2005, where MyTable.MyKey is an identity column.
-- Create empty temp table SELECT * INTO #TmpMikeMike FROM (SELECT m1.* FROM MyTable m1 LEFT OUTER JOIN MyTable m2 ON m1.MyKey=m2.MyKey WHERE 1=0 ) dt INSERT INTO #TmpMike SELECT TOP 1 * FROM MyTable SELECT * from #TmpMike
EDIT
THIS WORKS, with no errors...
-- Create empty temp table SELECT * INTO #Tmp_MyTable FROM (SELECT m1.* FROM MyTable m1 LEFT OUTER JOIN MyTable m2 ON m1.KeyValue=m2.KeyValue WHERE 1=0 ) dt ... WHILE ... BEGIN ... INSERT INTO #Tmp_MyTable SELECT TOP (@n) * FROM MyTable ... END
however, what is your real problem? Why do you need to loop while inserting "*" into this temp table? You may be able to shift strategy and come up with a much better algorithm overall.
Solution:2
You could try
SET IDENTITY_INSERT #Tmp_MyTable ON -- ... do stuff SET IDENTITY_INSERT #Tmp_MyTable OFF
This will allow you to select into #Tmp_MyTable
even though it has an identity column.
But this will not work:
-- Create empty temp table SELECT * INTO #Tmp_MyTable FROM MyTable WHERE 1=0 ... WHILE ... BEGIN ... SET IDENTITY_INSERT #Tmp_MyTable ON INSERT INTO #Tmp_MyTable SELECT TOP (@n) * FROM MyTable SET IDENTITY_INSERT #Tmp_MyTable OFF ... END
(results in the error "An explicit value for the identity column in table '#Tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.")
It seems there is no way without actually dropping the column - but that would change the order of columns as OP mentioned. Ugly hack: Create a new table based on #Tmp_MyTable ...
I suggest you write a stored procedure that creates a temporary table based on a table name (MyTable
) with the same columns (in order), but with the identity property missing.
You could use following code:
select t.name as tablename, typ.name as typename, c.* from sys.columns c inner join sys.tables t on c.object_id = t.[object_id] inner join sys.types typ on c.system_type_id = typ.system_type_id order by t.name, c.column_id
to get a glimpse on how reflection works in TSQL. I believe you will have to loop over the columns for the table in question and execute dynamic (hand-crafted, stored in strings and then evaluated) alter statements to the generated table.
Would you mind posting such a stored procedure for the rest of the world? This question seems to come up quite a lot in other forums as well...
Solution:3
EDIT Toggling IDENTITY_INSERT as suggested by Daren is certainly the more elegant approach, in my case I needed to eliminate the identity column so that I could reinsert selected data into the source table
The way that I addressed this was to create the temp table just as you do, explicitly drop the identity column, and then dynamically build the sql so that I have a column list that excludes the identity column (as in your case so the proc would still work if there were changes to the schema) and then execute the sql here's a sample
declare @ret int Select * into #sometemp from sometable Where id = @SomeVariable Alter Table #sometemp Drop column SomeIdentity Select @SelectList = '' Select @SelectList = @SelectList + Coalesce( '[' + Column_name + ']' + ', ' ,'') from information_schema.columns where table_name = 'sometable' and Column_Name <> 'SomeIdentity' Set @SelectList = 'Insert into sometable (' + Left(@SelectList, Len(@SelectList) -1) + ')' Set @SelectList = @SelectList + ' Select * from #sometemp ' exec @ret = sp_executesql @selectlist
Solution:4
I have wrote this procedure as compilation of many answers to automatically and fast drop column identity:
CREATE PROCEDURE dbo.sp_drop_table_identity @tableName VARCHAR(256) AS BEGIN DECLARE @sql VARCHAR (4096); DECLARE @sqlTableConstraints VARCHAR (4096); DECLARE @tmpTableName VARCHAR(256) = @tableName + '_noident_temp'; BEGIN TRANSACTION -- 1) Create temporary table with edentical structure except identity -- Idea borrowed from https://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table -- modified to ommit Identity and honor all constraints, not primary key only! SELECT @sql = 'CREATE TABLE [' + so.name + '_noident_temp] (' + o.list + ')' + ' ' + j.list FROM sysobjects so CROSS APPLY ( SELECT ' [' + column_name + '] ' + data_type + CASE data_type WHEN 'sql_variant' THEN '' WHEN 'text' THEN '' WHEN 'ntext' THEN '' WHEN 'xml' THEN '' WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')' ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '') END + ' ' /* + case when exists ( -- Identity skip select id from syscolumns where object_name(id)=so.name and name=column_name and columnproperty(id,name,'IsIdentity') = 1 ) then 'IDENTITY(' + cast(ident_seed(so.name) as varchar) + ',' + cast(ident_incr(so.name) as varchar) + ')' else '' end + ' ' */ + CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END + 'NULL' + CASE WHEN information_schema.columns.column_default IS NOT NULL THEN ' DEFAULT ' + information_schema.columns.column_default ELSE '' END + ',' FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = so.name ORDER BY ordinal_position FOR XML PATH('') ) o (list) CROSS APPLY( SELECT CHAR(10) + 'ALTER TABLE ' + @tableName + '_noident_temp ADD ' + LEFT(alt, LEN(alt)-1) FROM( SELECT CHAR(10) + ' CONSTRAINT ' + tc.constraint_name + '_ni ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')' + COALESCE(CHAR(10) + r.list, ', ') FROM information_schema.table_constraints tc CROSS APPLY( SELECT '[' + kcu.column_name + '], ' FROM information_schema.key_column_usage kcu WHERE kcu.constraint_name = tc.constraint_name ORDER BY kcu.ordinal_position FOR XML PATH('') ) c (list) OUTER APPLY( -- https://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema SELECT ' REFERENCES [' + kcu1.constraint_schema + '].' + '[' + kcu2.table_name + ']' + '([' + kcu2.column_name + ']) ' + CHAR(10) + ' ON DELETE ' + rc.delete_rule + CHAR(10) + ' ON UPDATE ' + rc.update_rule + ', ' FROM information_schema.referential_constraints as rc JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name) JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position) WHERE kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name ) r (list) WHERE tc.table_name = @tableName FOR XML PATH('') ) a (alt) ) j (list) WHERE xtype = 'U' AND name NOT IN ('dtproperties') AND so.name = @tableName SELECT @sql as '1) @sql'; EXECUTE(@sql); -- 2) Obtain current back references on our table from others to reenable it later -- https://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema SELECT @sqlTableConstraints = ( SELECT 'ALTER TABLE [' + kcu1.constraint_schema + '].' + '[' + kcu1.table_name + ']' + ' ADD CONSTRAINT ' + kcu1.constraint_name + '_ni FOREIGN KEY ([' + kcu1.column_name + '])' + CHAR(10) + ' REFERENCES [' + kcu2.table_schema + '].[' + kcu2.table_name + ']([' + kcu2.column_name + '])' + CHAR(10) + ' ON DELETE ' + rc.delete_rule + CHAR(10) + ' ON UPDATE ' + rc.update_rule + ' ' FROM information_schema.referential_constraints as rc JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name) JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position) WHERE kcu2.table_name = 'department' FOR XML PATH('') ); SELECT @sqlTableConstraints as '8) @sqlTableConstraints'; -- Execute at end -- 3) Drop outer references for switch (structure must be identical: http://msdn.microsoft.com/en-gb/library/ms191160.aspx) and rename table SELECT @sql = ( SELECT ' ALTER TABLE [' + kcu1.constraint_schema + '].' + '[' + kcu1.table_name + '] DROP CONSTRAINT ' + kcu1.constraint_name FROM information_schema.referential_constraints as rc JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name) JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position) WHERE kcu2.table_name = @tableName FOR XML PATH('') ); SELECT @sql as '3) @sql' EXECUTE (@sql); -- 4) Switch partition -- http://www.calsql.com/2012/05/removing-identity-property-taking-more.html SET @sql = 'ALTER TABLE ' + @tableName + ' switch partition 1 to ' + @tmpTableName; SELECT @sql as '4) @sql'; EXECUTE(@sql); -- 5) Rename real old table to bak SET @sql = 'EXEC sp_rename ' + @tableName + ', ' + @tableName + '_bak'; SELECT @sql as '5) @sql'; EXECUTE(@sql); -- 6) Rename temp table to real SET @sql = 'EXEC sp_rename ' + @tmpTableName + ', ' + @tableName; SELECT @sql as '6) @sql'; EXECUTE(@sql); -- 7) Drop bak table SET @sql = 'DROP TABLE ' + @tableName + '_bak'; SELECT @sql as '7) @sql'; EXECUTE(@sql); -- 8) Create again doped early constraints SELECT @sqlTableConstraints as '8) @sqlTableConstraints'; EXECUTE(@sqlTableConstraints); -- It still may fail if there references from objects with WITH CHECKOPTION -- it may be recreated - https://stackoverflow.com/questions/1540988/sql-2005-force-table-rename-that-has-dependencies COMMIT END
Use is pretty simple:
EXEC sp_drop_table_identity @tableName = 'some_very_big_table'
Benefits and limitations:
- It uses switch partition (applicable to not partitioned tables too) statement for fast move without full data copy. It also apply some conditions for applicability.
- It make on the fly table copy without identity. Such solution I also post separately and it also may need tuning on not so trivial structures like compound fields (it cover my needs).
- If table included in objects with schema bound by CHECKOUPTION (sp, views) it prevent do switching (see last comment in code). It may be additionally scripted to temporary drop such binding. I had not do that yet.
All feedback welcome.
Solution:5
Most efficient way to drop identity columns (especially for large databases) on SQL Server is to modify DDL metadata directly, on SQL Server older than 2005 this can be done with:
sp_configure 'allow update', 1 go reconfigure with override go update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column where id = object_id('table_name') and name = 'column_name' go exec sp_configure 'allow update', 0 go reconfigure with override go
SQL Server 2005+ doesn't support reconfigure with override, but you can execute Ad Hoc Queries when SQL Server instance is started in single-user mode (start db instance with -m flag, i.e. "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m", make sure to run as Administrator) with Dedicated Admin Console (from SQL Management Studio connect with ADMIN: prefix, i.e. ADMIN:MyDatabase). Column metdata is stored in sys.sysschobjs internal table (not shown without DAC):
use myDatabase update sys.syscolpars set status = 1, idtval = null -- status=1 - primary key, idtval=null - remove identity data where id = object_id('table_name') AND name = 'column_name'
More on this approach on this blog
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon