BULK

SQL Server

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.

Oracle

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.