Creat a replica of sql table using query

Question

I need a query to create a table which is the exact replica but with different table name and without any data from the source table using a sql query!

Answer 1

• SQL Server Management Studio
• Object Explorer
• Connect -> Your server
• Databases -> Choose Database
• Tables
• Right Click Your Table
• Script Table as -> Create To -> New Query Editor Window

Answer 2

This can help you:

CREATE TABLE foo AS SELECT...

Read more here

Answer 3

For MySQL, you can call SHOW CREATE TABLE table_name;

It will display a CREATE TABLE query. Simply change the table name in that query and you're good to go.

http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html

Answer 4

If you use Postgresql:

CREATE TABLE LIKE table_name

http://www.postgresql.org/docs/8.1/static/sql-createtable.html

Answer 5

You can try this

SELECT * INTO Table_Copy
FROM Table
where 1=2

It will create a empty table with the same structure.

Answer 6

select * into newtablename from sourcetablename
go
truncate newtablename
go

That will result in an exact copy but it also copies the data at first which you remove with the truncate statement.

Answer 7

Jonathan has it (upvoted), and you should probably go with that because it's more portable. I normally use something similar:

SELECT TOP 0 * INTO [New_Table] FROM [Old_Table]

I think this better expresses what you're doing, but I like Jonathan's because 'TOP 0' is SQL Server specific, and so his is more portable.