Microsoft SQL Server 2000 Administrators Companion
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.