Insert records in to table that has primary key identity seed

Question

Insert records in to table that has primary key identity seed

scotsditch

Ten Centuries

Points: 1020
More actions
October 2, 2018 at 12:51 pm

#377867

I'm trying to insert records from one table in to another table like the example below. The two tables have identical schema, except table1 has an extra column that is a primary key and an identity seed that increments by 1. Can I just use an insert statement like the one below to insert the records from table2 or do I need to add something to create the primary key field in table2?
Code:
select * into table1 from table2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11412 More actions · Answer 1

First of all, why????

secondly, you'd need to set identity insert on before inserting to the second table (I assume there is an identity column on it, but it's not clear). Then you should list out columns names i.e. Insert into TableB (Col1, Col2...) select Col1, Col2... from TableA

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ScottPletcher SSC Guru Points: 101134 More actions · Answer 2

If you want to add an IDENTITY column in table1 that is not in table2, you can do this:

select IDENTITY(int, 1, 1) AS id, *
into table1
from table2

You don't have to make it a pk, but you could, using the standard way to create a PK:
ALTER TABLE dbo.table1 ADD CONSTRAINT ... PRIMARY KEY ( id ) ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Thom A SSC Guru Points: 99134 More actions · Answer 3

I think the OP is saying that both tables already exist. If that is the case, you can't use the INTO clause, you need to use an INSERT statement. If the extra column is already an IDENTITY then SQL Server will handle the population of those values.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 4

Thom A - Tuesday, October 2, 2018 1:14 PM
I think the OP is saying that both tables already exist. If that is the case, you can't use the INTO clause, you need to use an INSERT statement. If the extra column is already an IDENTITY then SQL Server will handle the population of those values.

And you need to specify all of the columns EXCEPT the identity column in the destination table - or you set identity insert on the table and specify the identity value to be inserted in the SELECT.

INSERT INTO table1 (list of columns here - excluding identity column)
SELECT {list all columns here} FROM table2;

INSERT INTO table1 (list all columns here)
SELECT identity(int, 1, 1) AS identity_column, {rest of columns here} FROM table2;

Either way - if you are inserting data into a table that has an identity column, you must include the list of columns being inserted.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

scotsditch - Tuesday, October 2, 2018 12:51 PM
Can I ... ?

Did you try it before asking?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99134 More actions · Answer 6

Jeffrey Williams 3188 - Tuesday, October 2, 2018 1:40 PM
Either way - if you are inserting data into a table that has an identity column, you must include the list of columns being inserted.

This isn't true, actually. Try the following.

CREATE TABLE dbo.SomeTable1 (SomeString varchar(50),
           SomeNumber int);
CREATE TABLE dbo.SomeTable2 (SomeID int IDENTITY(1,1),
           SomeString varchar(50),
           SomeNumber int);
GO
INSERT INTO dbo.SomeTable1
VALUES ('dasfjkpsdhfkljsdbhlbjksdfl',6546),
    ('asdlfkjasdbghklofasdghljkfbghl',981),
    ('asdlkidfgsdlkfgld',4);
INSERT INTO dbo.SomeTable2
SELECT *
FROM dbo.SomeTable1;
GO
INSERT INTO dbo.SomeTable2
VALUES ('qwerty',12345);
GO
SELECT *
FROM dbo.SomeTable2;
GO
DROP TABLE dbo.SomeTable1;
DROP TABLE dbo.SomeTable2;

I've tested, and this works on SQL Server 2008, 2012 and 2017. Provided that the 2 datasets have the same definition (excluding the IDENTITY) then SQL Server is "clever" enough to know how to handle the INSERT. I however, don't recommend it. You should always supply a list of columns for an INSERT. For example, if the destination table's definition changes, the INSERT can (probably) continue to work without being amended (provided the new column(s) are NULLable, and a referenced column doesn't have a breaking change, etc).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk