SQL Reference

BULK

BULK is a SQL command that facilitates the rapid transfer of large amounts of data from a file into a SQL Server database. The main purpose is to provide an efficient mechanism for loading data into tables or views in SQL Server. Its primary benefit is in its speed, which is considerably faster than single-row insert statements. It can be used in combination with other SQL commands to streamline data import and processing tasks.

Example

CREATE TABLE Prices (
    ItemID INT,
    Price INT
)

BULK INSERT Prices
FROM 'c:\data\prices.txt'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

Output

(3 row(s) affected)

Explanation

In this example, a table Prices with columns ItemID and Price is firstly created. The BULK INSERT statement is then utilized to import data, specified in a text file (prices.txt), into the Prices table. The FIELDTERMINATOR and ROWTERMINATOR options define the delimiters separating fields and rows in the source data. The output simply notifies that three rows have been inserted into the Prices table using the BULK INSERT operation.

Example

DECLARE
   TYPE t_names IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   v_names t_names;
BEGIN
   SELECT first_name BULK COLLECT INTO v_names FROM employees;

   FOR i IN 1..v_names.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(v_names(i));
   END LOOP;
END;

Output

John
Jane
Steve
Tom

Explanation

The example declares a collection, t_names, to hold employee names. It selects the first_name from the employees table using the BULK COLLECT clause which bulk fetches the data and stores it into v_names. The loop then prints out each name in the output.