Copying a table in SQL Server

Is it possible to copy a table (with def开发者_如何学运维inition, constraints, identity) to a new table?

• 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

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

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...

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