Using SELECT INTO to Create a Test Table

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