Clone a table in sql

Question

How can I create a table from another table?

Option 1

SELECT * INTO target FROM  source WHERE 1 = 2

But this does not create all the constraints.

Option 2

right click on the table -> Script table as -> Create to.: this does not work for me as I want to do this frequently for over 30 tables.

Any ideas?

I think you have a great starting point at this: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table — Ako, Mar 05 '15 at 11:43

score 0 · Answer 1 · answered Mar 05 '15 at 11:46

You can use Sql Server Management Objects to duplicate your choice of "table -> Script table as -> Create to". See an example in here. Microsoft help is here. After you create your scripts, you use search and replace to give new names.

Note that you only create necessary DML this way including foreign key and primary keys. After all these you also need to execute if you also need to add data to your tables.

 INSERT INTO target SELECT * FROM  source

Clone a table in sql

1 Answers1