Occasionally, you will want to add rows to a table that does not exist. The select into statement will create a table and load the result set into the table. The syntax of the select into statement is:

When using a select into statement, each column in the select list must have a column header. Moreover, unlike a normal select list, each column header must only appear once in the select list.

To create an empty table that matches an existing table, use the select into statement where the WHERE clause returns no rows. Typically, the where clause would be where 1=2.

The following code sample uses the select into statement to create a new table, Persons, with three columns containing all of the rows from the Employee table.

select FName, LName, HomePhone
Into Persons
From Employee

The database must be configured to allow the select into to work. You will learn more about how to do this later in the course.

After performing a select into, you should immediately perform a full backup, since nothing is logged except for page allocation. Until you perform a full or differential backup, you can not make a usable transaction log backup.
Often times you will need to load data from an operating system file into a database. SQL Server provides two ways to do this. The next lesson will cover one of the ways--the bulk insert statement.