How to drop IDENTITY property of column in SQL Server 2005

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.

From stackoverflow

• 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...

  Michael Pryor : don't you mean the revers? turn ON insert first...

  NYSystemsAnalyst : @tvanfosson - michaelpryor is right. You need to turn identity_insert on to allow the insertion. Then, turn it off when finished.

  tvanfosson : @NY -- right, don't know what I was thinking. Previous comment removed.

  Joe : Doesn't work for me, see repro above.

  Daren Thomas : Joe, I get the same error :( let's have closer look...

  KM : for the easy workaround, see my solution below... I use a derived table with a join, to drop the identity from the "INTO" table...

• 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
  

• 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.

  Joe : Doesn't work for me. I tried modifying my repro as you suggested, see above.