SQL SELECT INTO Statement

  • SELECT INTO copies data from one table into a new table.
  • SELECT INTO creates a new table located in the default filegroup.
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL SELECT INTO syntax


The general syntax is:
SELECT column-names
  INTO new-table-name
  FROM table-name
 WHERE EXISTS 
      (SELECT column-name
         FROM table-name
        WHERE condition)

The new table will have column names as specified in the query.



SQL SELECT INTO Example



Problem: Copy all suppliers from USA to a new SupplierUSA table.

SELECT * INTO SupplierUSA
  FROM Supplier
 WHERE Country = 'USA'


Results: 4 rows affected


Here are the records in the newly created table SupplierUSA:

Id CompanyName ContactName City Country Phone Fax
2 New Orleans Cajun Delights Shelley Burke New Orleans USA (100) 555-4822 NULL
3 Grandma Kelly's Homestead Regina Murphy Ann Arbor USA (313) 555-5735 (313) 555-3349
16 Bigfoot Breweries Cheryl Saylor Bend USA (100) 555-4822 NULL
19 New England Seafood Cannery Robb Merchant Boston USA (617) 555-3267 (617) 555-3389