While working with SQL Server tables, especially when you are testing a certain code, you would want to create a test table that is similar to the actual / original table. That way you don’t have to worry about accidental updates or data loss etc.
During such situations, creating an an exact replica of that table can be achIeved using SELECT INTO statement. Below is a script to create an exact replica of Person.Address table found in AdventureWorks Sample Database.
SELECT * INTO AdventureWorks.dbo.Person_Address_Test FROM AdventureWorks.Person.Address
Below is a screen shot of the query run on AdventureWorks DB on a SQL Server 2008 R2 Instance.
What this SELECT INTO does is, it creates a new table (if it does not exist) and inserts the data into that table. Since we have not filtered any rows with a WHERE clause, all the data from the table Person.Address is now readily available in the Test table. If there is a table that already exists with that chosen name, the statement fails, indicating there is already a table with that name.
Now you run any command on the newly created table, the below is a simple select statement with top 10 *.
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.
— Bru Medishetty
this seems to be an effecient way of copying a table,are there anyother ways? Is this a frequently used tool?
Yes this is one of the ways to quickly create a table. But with every different way there is + and -, using this way, you will not have all constraint of the original table.
Yes there are other ways to create a table (just a table).. by right clicking on the table and selecting Script Table as —-> Create To —-> New Query Editor Window or to Clipboard or to a file.. Doing this will provide the exact script to create the source table along with any constraints..
is there a way to also copy the constraints at the same time
thanks, didnt read the last line, you answered my question