CODEWITHSUNDEEP
sqlsql-server
Haoest
Haoest

Reputation: 13916

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

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.

Upvotes: 13

Views: 40162

Answers (9)

sǝɯɐſ
sǝɯɐſ

Reputation: 2528

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

Upvotes: 10

DK.
DK.

Reputation: 3243

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 column id

UPD: Here's an example I've tested in SQL Server 2008:

create table T(ID int identity(1,1) not null, Value nvarchar(50))
insert into T (Value) values (N'Hello T!')
select top 0 * into #tmp from T
alter table #tmp drop column ID
insert into #tmp (Value) values (N'Hello #tmp')
insert into T select * from #tmp
drop table #tmp
select * from T
drop table T

Upvotes: 12

Jasmine
Jasmine

Reputation: 4029

set identity_insert on

Use this.

Upvotes: 1

Rob Packwood
Rob Packwood

Reputation: 3808

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

Upvotes: 1

Rob Allen
Rob Allen

Reputation: 17749

  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.

Upvotes: -1

BCS
BCS

Reputation: 78673

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96630

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)

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89721

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.

Upvotes: 1

Orion Adrian
Orion Adrian

Reputation: 19573

SET IDENTITY_INSERT ON

INSERT command

SET IDENTITY_INSERT OFF

Upvotes: 13

Related Questions