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

-------------Problems Reply------------

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


INSERT command


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.

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)

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


set identity_insert on

Use this.

  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.

Category:sql Views:1 Time:2008-09-23
Tags: sql sql server

Related post

  • insert into select from in a table with identity column 2010-03-16

    I am using a insert into select from statement to insert the result set of a query into a table containing an identity column. I am consistently getting an error Cannot insert duplicate key row in object 'dbo.TABLE1' with unique index 'IX_TABLE1'. Pl

  • Inserting the Identity of another Insert from Select? 2012-04-27

    Is This Possible? Here is something I'm looking for, executed together: First, it would execute the INSERT based on how many rows in the SELECT INSERT INTO TABLE2 (xID, NAME) SELECT xID, NAME FROM TABLE Then getting the @@IDENTITY of each INSERTED RO


    so I have an old database that I'm migrating to a new one. The new one has a slightly different but mostly-compatible schema. Additionally, I want to renumber all tables from zero. Currently I have been using a tool I wrote that manually retrieves th

  • Oracle bug? SELECT returns no dupes, INSERT from SELECT has duplicate rows 2009-12-08

    I'm getting some strange behaviour from an Oracle instance I'm working on. This is 11gR1 on Itanium, no RAC, nothing fancy. Ultimately I'm moving data from one Oracle instance to another in a data warehouse scenario. I have a semi-complex view runnin

  • SQL Server inserts and select taking long time 2010-12-13

    We have a table with about 20 columns as shown below: We need to do 1000 records insert and select later also produces about 1000 records. inserts were tried to be done in 2 ways: parallel via parallel.For c# loop sql adapter inserting whole dataset

  • Insert..Select into InnoDB table with commit after each insert? 2011-05-05

    I just finished creating a new partitioned table to replace an old, non-partitioned table (renamed for safekeeping). I copied the newest data from the old table into the new table at the time I created it, but I still have roughly half the data left

  • can SQL insert using select return multiple identities? 2012-01-24

    I am insertnig into a table using a selection INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = 'CA' say i have an identity column in california_authors. Can i get all the ids inserted b

  • How to insert dates selected from drop downs into mySQL table using php? 2009-04-10

    When you have 5 dropdowns for date selection (Y, m,d, H,i) how do you insert the selected date and time into a single column of a mySQL table as a datetime using PHP5? I know they need to be combined in a string such as: $DateTime="$Year-$Month-$Day

  • differences between INSERT select and insert into select? 2009-08-27

    I often see two styles, INSERT select and insert into select, what are the differences? Are they the same? I am using SQL Server 2008 Enterprise. Here are two samples. INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_f

  • How to improve INSERT INTO - SELECT locking behavior 2010-04-14

    In our production database, we ran the following pseudo-code SQL batch query running every hour: INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true) Now this query itself does not need to

  • vista can not allow me to insert or select record msaccess from database when i login from user not admin 2010-06-21

    i have little problem in using my application application having msaccess database if i install it on xp i can easily insert or select record from database but if i install it on vista. i can't access my msaccess database. if i login as admin it work

  • MySql 5.1.x: insert into select .. encode strings 2010-12-04

    i have 2 table with the same DEFAULT CHARSET=latin1 If i copy some data from one table to the another with the command 'Insert into () Select ()', MySql encode the strings. Example: 2461/P/J- -- RESO N° RM10 became 2461/P/J- -- RESO N° RM10 note the

  • MySQL Repair with keycache following INSERT FROM SELECT 2010-12-30

    I am not an expert with MySQL, and I am trying to understand why the table shows Repair with Keycache and what I can do to correct it at this point. I have done some searching, but haven't been able to find much on this particular cause... The situat

  • Database with 5 Tables with Insert and Select 2011-01-06

    my problem is that i have 5 tables and need inserts and selects. what i did is for every table a class and there i wrote the SQL Statements like this public class Contact private static String IDCont = "id_contact"; private static String NameCont = "

  • insert and select record in parent child table 2011-01-15

    I have two table CUSTOMER as parent and PROFILE as child with one to many relationship.I want to insert and select data from both table simultaneously how? --------------Solutions------------- Since u are using jdbc , to select parent and child write

  • Insert and select date from SQL Server database 2011-03-16

    I have got the following exception when try to select data from SQL Server or inserting data in in with a C# windows application. I am passing the date in where clause of select query in single quotes like this '16/03/2011' The exception message is s

  • MySQL Insert if selected value > x 2011-04-06

    I am trying to write a MySQL insert that only inserts based on the result of a select query. I have found some information on using if-then-endIf logic in queries but haven't had any luck getting one to work. Then general idea that I am trying to acc

  • How can I check for duplicates before inserting into a table when inserting by select 2011-04-08

    How can I check for duplicates before inserting into a table when inserting by select: insert into table1 select col1, col2 from table2 I need to check if table1 already has a row with table1.col1.value = table2.col1.value, and if yes, then exclude t

  • What does insert(), delete(), select() and update() of the MyBatis SqlSession Interface return? 2011-05-05

    From the API Doc of mybatis i can just see, that insert, delete, select and update has an int as the returntype. But there is no documentation about what the meaning of the returned integers is. Does this has something to do with Success of the opera

Copyright (C), All Rights Reserved.

processed in 0.114 (s). 11 q(s)