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 anyways 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

SET IDENTITY_INSERT ON

INSERT command

SET IDENTITY_INSERT OFF

Answer 2

set identity_insert on

Answer 3

Not with SELECT * - if you selected every column but the identity, it will be fine. The only way I can see is that you could do this by dynamically building the INSERT statement.

Answer 4

Just list the colums you want to re-insert, you should never use select * anyway. If you don't want to type them ,just drag them from the object browser (If you expand the table and drag the word, columns, you will get all of them, just delete the id column)

Answer 5

As identity will be generated during insert anyway, could you simply remove this column from #tmp before inserting the data back to T?

alter table #tmp drop id

Answer 6

Might an "update where T.ID = #tmp.ID" work?

Answer 7

1. it gives me a chance to preview the data before I do the insert
2. I have joins between temp tables as part of my calculation; temp tables allows me to focus on the exact set data that I am working with. I think that was it. Any suggestions/comments?

For part 1, as mentioned by Kolten in one of the comments, encapsulating your statements in a transaction and adding a parameter to toggle between display and commit will meet your needs. For Part 2, I would needs to see what "calculations" you are attempting. Limiting your data to a temp table may be over complicating the situation.

Answer 8

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