Sql Query Create a new Table

Question

Can anyone offered syntax for a sql query that takes an already created table and inserts some of the data into a new table with a new id(primary key)? So far I tried this

SELECT ((ID INT IDENTITY(1,1) PRIMARY KEY), column1, column2, column3)
INTO table_name
FROM table_name

I keep coming up with a syntax error.

Just for understanding: Why don't you just copy the 'from'-table and change the primary key afterwards with an "alter-table" statement? — Kai
– Kai, Commented May 3, 2012 at 15:06
You can't insert a column declaration into a table row. And your example is selecting into table_name from table_name - you might want to update your example to make it clearer that you (do you?) mean INTO target_table FROM source_table. — Cylindric
– Cylindric, Commented May 3, 2012 at 15:12

Darshana · Accepted Answer · 2012-05-03 15:15:50Z

4

SELECT column1, column2, column3
INTO table_name_new
FROM table_name_old

use like this

answered May 3, 2012 at 15:15

Darshana

2,5486 gold badges30 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitch Wheat · Accepted Answer · 2012-05-03 15:07:56Z

3

INSERT INTO newtable (column1, column2, column3)
SELECT (column1, column2, column3) 
FROM table_name

answered May 3, 2012 at 15:07

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Comments

kemiller2002 · Accepted Answer · 2012-05-03 15:06:03Z

2

INSERT INTO new_table
(Column1, Column2 . . . )

SELECT Column1, Column2 FROM old_table

Don't include the identity column (the primary key with the auto-generated numbers) in the insert list.

answered May 3, 2012 at 15:06

kemiller2002

116k28 gold badges202 silver badges255 bronze badges

Comments

Rahul · Accepted Answer · 2012-05-03 15:09:35Z

2

Just make it like below, Aa new id will be automatically created while inserting the record

insert into table1 (col1,col2,col3) select col1,col2,col3 from table2;

answered May 3, 2012 at 15:09

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Paul Keister · Accepted Answer · 2012-05-03 15:18:45Z

1

According to the documentation on MSDN, the IDENTITY property will transfer through with a SELECT INTO clause, but constraints will not. That means you can create a new table with an IDENTITY column if you include the original IDENTITY column from your source table, but the column in the new table won't be a primary key.

The sane way to do this is to use ALTER TABLE after the table is created, as @Kai has suggested.

answered May 3, 2012 at 15:18

Paul Keister

13.1k5 gold badges50 silver badges77 bronze badges

Collectives™ on Stack Overflow

Sql Query Create a new Table

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related