See Also: Main_Page - Business Intelligence

Similar in functionality to BCP, BULK INSERT lets users import data into SQL Server from within Query Analyzer or a T-SQL program. This command became available with SQL Server 7.0. Unlike BCP, Bulk Insert is executed from the Query Analyzer. This gives the developer an advantage because BULK INSERT can be used within any T-SQL program. This also allows a developer to use BULK INSERT within a transaction and decide whether to insert data into a table depending on the outcome of a user-defined transaction. Unlike executing BCP from within T-SQL code, you can execute BULK INSERT in any database context, presuming you have appropriate permissions. However, BULK INSERT is limited to importing data into SQL Server, it cannot export SQL Server data into any other format. As with BCP, a user performing bulk insert has to have INSERT permission on the destination table. Unlike BCP, only members of SYSTEM ADMINISTRATORS and BULK INSERT ADMINISTRATORS fixed server roles can execute bulk insert statement.

Since BULK INSERT runs in-process with the SQL Server database engine SQL Server can avoid passing data through the network abstraction layer of the client API (which is the case with both BCP and DTS). This makes BULK INSERT quite a bit faster than either BCP or DTS.

As with BCP, in order to successfully run a non-logged BULK INSERT statement, the SELECT INTO / BULK COPY option has to be set to true. Keep in mind that the table that you are trying to populate must exist before running BULK INSERT.

SELECT INTO statement

The Select INTO statement creates a new table with the columns specified in the SELECT portion of the statement and the same data types as columns in the source table. This table is populated with the results of the query. The Select INTO construct could be used to load results of a multi-table join into a temporary (or permanent) table that will be easier to query. For example, the following query creates a prod_category table with productname and categoryname columns:

SELECT TOP 50 productname, categoryname   INTO prod_category  FROM products p, categories c  WHERE  p.categoryid = c.categoryid

results:

(50 row(s) affected)

The Select portion of SELECT INTO can be a distributed query, so the newly created table could also be populated with the values retrieved from a remote data source. 

To be able to use SELECT INTO the "SELECT INTO / BULKCOPY" option needs to be set ON for the database where you are executing the command. You can change this option by using sp_dboption, or the ALTER DATABASE statement. To retrieve the current setting for this option run sp_dboption or examine the results of the following:

SELECT DATABASEPROPERTY ('database_name', 'isbulkcopy')