[RESOLVED] Duplicate table in mssql server

[robertx]

Is there an easy way to duplicate a table (schema & data) using MSSQL Server Management Studio (Express)? I want to create a duplicate table before I run a data update. If there's a problem, I want to be able to easily rollback to the previous version.

[dee-u]

You can get the script of a table by right clicking on it and selecting Script Table As -> CREATE TO -> New Query Editor Window.

But be aware that you cannot have 2 tables having the same names, you must have a different name for the other table. And when you are saving then you can just insert into that table the record being updated/inserted. That is called Audit Logging.

[nubie]

or you can run a query similar to this:

SELECT *
INTO Persons_Backup
FROM Persons

[GaryMazzone]

If you want a empty table then

SELECT * INTO Person_Emprty from Person WHERE 1 = 2

That will duplicate the fields and their sizing, it will not rebuild index, constraints, PKs or FKs

[robertx]

Thanks. That did it.