Copying table constraints from select * into

Question

Does select * into B from A also copy constraints of A on B ? If not, then how can I copy constraints?

Yes, I tried it and it did not copy any constraints so I assume I must be missing something. — Reeya Oberoi, Jul 30 '14 at 17:30

Lexi Tramel · Accepted Answer · 2020-09-11T17:50:07.657

2

No, not in SQL-Server. You would need to specify the constraints and indexes on the new table manually. If you're using SSMS, using the Script As... Create To functionality can get you a sample script to create a new table with the same constraints and structure.

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver15

Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.

edited Sep 11 '20 at 17:50

answered Jul 30 '14 at 17:09

Lexi Tramel

447
5
17

Thanks @Chris. When you say "No, not in SQL Server", do you mean it happens in other databases? If yes, which ones? – Reeya Oberoi Jul 30 '14 at 17:45
1

MySQL's `CREATE TABLE x LIKE y` copies structure and primary key, but not constraints. Newer Postgres versions can copy everything using `create table x ( like y INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );`. – Lexi Tramel Jul 30 '14 at 18:01
By any chance have things changed in more recent MS SQL iterations? Per the OP, is this now possible using T-SQL? I ask since this question and the answers are a bit old and I'm hoping that SQL-Server has stepped up and added functionality to accomplish `SELECT * INTO my_tbl_tmp FROM my_tbl` and include PK/FK/constraints/etc... – Rockin4Life33 Sep 10 '20 at 16:37
1

@Rockin4Life33 I just rechecked the docs (and added a link to the answer), and no, it is still the case that none of indexes, constraints, nor triggers are transferred automatically with a `SELECT... INTO` statement. – Lexi Tramel Sep 11 '20 at 17:51

score 2 · Answer 2 · answered Jul 30 '14 at 17:10

You can not directly copy the constraint from one table to another table, first you should copy the table Structure with indexes and constraint, to do this

Please follow the instructions below:

In SSMS right click on the table, script create.
Change the name in the generated script to NewTable

insert into NewTable select * from OldTable -- note that it may be slow if the Old is big enough.

score 0 · Answer 3 · answered Jul 30 '14 at 17:10

It will not copy constraints. If you want two tables to be set up with the same constraints, you have to do it manually by running the create table/constraint statements. You can have sql server create the sql statements from the existing table though. Using Sql Server Studio, in the Object Explorer right click the table and select Script As and select the options you want then change the table name as needed.

Copying table constraints from select * into

3 Answers3