2

Is it possible to copy a table (with definition, constraints, identity) to a new table?

Improve this question

4 Answers 4

Reset to default
1
  • 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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

2 Comments

so we can't do without prior creation of new table definition?
This will create the table definition but it won't have any of the constraint, keys etc etc
0

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

Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.