Insert into ... Select *, how to ignore identity?

Question

I have a temp table with the exact structure of a concrete table T. It was created like this:

select top 0 * into #tmp from T

After processing and filling in content into #tmp, I want to copy the content back to T like this:

insert into T select * from #tmp

This is okay as long as T doesn't have identity column, but in my case it does. Is there any way I can ignore the auto-increment identity column from #tmp when I copy to T? My motivation is to avoid having to spell out every column name in the Insert Into list.

EDIT: toggling identity_insert wouldn't work because the pkeys in #tmp may collide with those in T if rows were inserted into T outside of my script, that's if #tmp has auto-incremented the pkey to sync with T's in the first place.

Answer 1

See answers here and here:

select * into without_id from with_id
union all
select * from with_id where 1 = 0

Reason:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join, GROUP BY clause, or aggregate function.
• Multiple SELECT statements are joined by using UNION.
• The identity column is listed more than one time in the select list.
• The identity column is part of an expression.
• The identity column is from a remote data source.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

All credit goes to Eric Humphrey and bernd_k

Answer 2

INSERT INTO #Table SELECT MAX(Id) + ROW_NUMBER() OVER(ORDER BY Id)