from ms sql about copy a table

Question

Hi all is it possible to copy old table (with definition, constrains,identity) to new table. the new table not yet to be create. without prior creation of new table can we copy a table

thanks saj

Answer 1

No, not really, you have to script it out, then change the names

you can do this

select * into NewTable
FROM OldTable
WHERE 1 =2 --if you only want the table without data

but it won't copy any constraints

Answer 2

• Generate a CREATE script based on the table
• Modify the script to create a different table name
• Perform an INSERT from selecting everything from the source table

Answer 3

It's not the most elegant solution, but you could use a tool like the free Database Publishing Wizard from Microsoft.
It creates an SQL script of the table definition including data and including indexes and stuff. But you would have to alter the script manually to change the table name...

Answer 4

Another possibility:
I just found this old answer on SO. This script is an example to script the constraints of all tables, but you can easily change it to select only the constraints of "your" table.

So, you could do the following:

1. Create the new table with data like SQLMenace said (select * into NewTable from OldTable)
2. Add constraints, indexes and stuff by changing this SQL script