CODEWITHSUNDEEP
sqlsql-servert-sql
selvaraj
selvaraj

Reputation: 899

Copying a table in SQL Server

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

Upvotes: 2

Views: 503

Answers (4)

Christian Specht
Christian Specht

Reputation: 36451

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

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36451

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

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

  • 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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135171

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

Upvotes: 0

Related Questions