The SELECT...INTO Statement

3 4

Using the SELECT…INTO statement is not really a method of loading the database, but rather a way to create new tables from existing tables or from staging tables. The SELECT…INTO statement cannot be used to populate an existing table.

NOTE

---

For the SELECT…INTO statement to have permission to work, the database option select into/bulkcopy must be set to true. To set this option, use the following T-SQL statement:

 exec sp_dboption <database_name>, "select into/bulkcopy", TRUE

The syntax of the SELECT…INTO statement is shown here:

 SELECT <column_list> INTO   <new_table_name> <select_clause> 

The select_clause variable refers to statements that normally qualify a SELECT statement, such as FROM and WHERE. The SELECT…INTO statement is fairly straightforward and easy to use, as the following example demonstrates:

 exec sp_dboption "example", "select into/bulkcopy", TRUE GO SELECT order_id, contact_id, item_id, item_description, amount INTO newsales FROM stage GO exec sp_dboption "example", "select into/bulkcopy", FALSE GO 

Here the database name is "example," and the table that is created is newsales. The table from which the data is extracted is stage.