| Lesson 3 || The select into statement |
| Objective || Load data with the select into statement. |
Select into Statement using SQL-Server
Occasionally, you will want to add rows to a table that does not exist.
select into statement will create a table and load the result set into the table. The syntax of the select into statement is:
Select into Table
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.
Create an empty Table
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
Create a new table with select into
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
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.
Perform a backup
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.