How to clone a table for testing ?

Question

Hi All,

I am trying to test a script on a table and I wanted to make an exact copy of the table and name it differently so I can test my script on the new table

Thanks In advance

Answer 1

You can script your table by right click on it from SSMS and follow Script table as...

If you want data as well as the schema, you can right click on your database name and click on Tasks>>Generate Scripts>>..  follow the wizard >> last step is to click on Advanced tab to change a property under Options:Types of data to: Schema and data. (The default is Schema Only).

 You can run the script with a modified table name to clone your table and data.

Answer 2

Does select * into work for you?

Answer 3

create table t1 
( id int,
  name varchar(10)
  )
 insert t1 values (1,'swap')
 insert t1 values (1,'swap1')
 insert t1 values (1,'swap2')
 insert t1 values (1,'swap3')
 insert t1 values (1,'swap4')
--within same database     
select * into T2 from t1
     
     
-- to different database    
select * into test.dbo.T1 
from test1.dbo.t1  

Hope this helps!! 

Answer 4

I think best thing would be to script out the table using generate scripts wizard and apply it back after renaming (find and replace old name with new name) to preserve original constraints etc

http://blog.sqlauthority.com/2012/07/18/sql-server-generate-script-for-schema-and-data-sql-in-sixty-seconds-021-video/

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 5

Does select * into work for you?

Nope. that would  not replicate indexes, constraints etc as in main table so it wont be an exact clone

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs