BULK INSERT (TSQL Statement)

inserts records from a text file into the specified table.

Syntax

BULK INSERT <table_qname> FROM 'data_file' [ WITH ( <option> [ ,... ] ) ]

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

<option> :=
      CODEPAGE        = { 'ACP' | 'RAW' | 'code_page' }
    | FIELDTERMINATOR = 'field_terminator'
    | ROWTERMINATOR   = 'row_terminator'
    | FIRSTROW        = first_row
    | LASTROW         = last_row
    | KEEPIDENTITY
    | NEWIDENTITY
    | RTRIM

Arguments

<table_qname>
is the qualified or unqualified name of the target table into which records will be inserted.
‘data_file’
relative or absolute path to the text file to import.
  • Empty fields are converted to NULL.
  • The character 0x00 is converted to empty string for varchar or spaces for char.
  • If data_file is a relative path, the base directory is given by the server parameter SERVER_BULK_DIR.
CODEPAGE = { 'ACP' | 'RAW' | ‘code_page’ }
is the encoding of the text file.
  • 'ACP' is Microsoft windows1252 encoding, which is a superset of iso-8859-1.
  • 'RAW' is synonym of 'utf8'.
  • ‘code_page’ is e.g. 'utf8', 'UTF-8', 'iso8859-1', 'iso-8859-1', 'windows1252', etc. It is case insensitive and quite lenient about spelling.
  • By default, ‘code_page’ is 'utf8'.
FIELDTERMINATOR = ‘field_terminator’
specifies the field terminator used in the text file.
  • field_terminator contains one or many characters.
  • escape notation can be used, e.g. '\t', '\n', '\xFF', '\u0100'.
  • Any unicode character can be used. E.g. to use the unicode smiling face symbol: FIELDTERMINATOR = '\u263a'
  • By default, it is '\t'
ROWTERMINATOR = ‘row_terminator’
specifies the row terminator used in the text file.
  • row_terminator contains one or many characters.
  • escape notation can be used, e.g. '\t', '\n', '\xFF', '\u0100'.
  • By default, it is '\n', which is a special case as it considers that both \n and \r\n terminate lines.
FISTROW = first_row
specifies the first line to load. It is 1-based.
  • By default, it is 1.
LASTROW = last_row
specifies the last line to load. It is 1-based.
  • By default, it is 0, which means that all lines must be loaded.
KEEPIDENTITY
specifies that values of the IDENTITY column must be preserved as read from text file. The field in the text file cannot be empty.
  • If KEEPIDENTITY is not specified, the value of IDENTITY column in text file are ignored. The field in the text file can be empty.
    • Values for this column will be generated automatically during import.
  • For RSQL, if the target table contains an IDENTITY column, it is mandatory to specify either KEEPIDENTITY or NEWIDENTITY for safety reason.
NEWIDENTITY
specifies that new values for IDENTITY column will be generated automatically, and that the corresponding field in the text file will be ignored.
  • If the target table contains an IDENTITY column, it is mandatory to specify either KEEPIDENTITY or NEWIDENTITY for safety reason.
  • This is not a valid MS SQL Server option.
KEEPNULLS
specifies that empty fields will insert a NULL value in the table column, and not the DEFAULT value from the table definition.
  • in RSQL, this option is mandatory.
RTRIM
specifies that all fields from the text file must be right-trimmed.
  • This is useful if you have a text file with fixed-length columns, and want to convert fields with only spaces into NULL for varchar or char datatypes, instead or keeping the spaces.
  • This is not a valid MS SQL Server option.

Remarks

Conversion of fields for  BULK INSERT: table <-- text file   or
                          BULK EXPORT: table --> text file   is as follows:

table                              text file
=====                              =========

CHAR(p):
-------------------
    NULL                <-->       empty field
    p spaces            <--        0x00
    p spaces            <--        one or more spaces
    p spaces             -->       p spaces
    hello               <-->       hello

VARCHAR:
-------------------
    NULL                <-->       empty field
    empty string        <-->       0x00
    one or morespaces   <-->       one or more spaces
    hello               <-->       hello

INT:
-------------------
    NULL                <-->       empty field
    NULL                <--        one or more spaces
    0                   <-->       0
    123                 <-->       123

DATETIME:
-------------------
    NULL                <-->       empty field
    NULL                <--        one or more spaces
    2000.02.03          <-->       2000.02.03

See also BULK EXPORT.

Examples

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

CREATE TABLE mytable (
    a INT           NOT NULL IDENTITY,
    b VARCHAR(8)    NULL,
    c VARCHAR(6)    NULL,
    d CHAR(6)       NULL,
    e NUMERIC(12,2) NULL,
    f DATETIME      NULL
);
GO

INSERT mytable(b, c, d, e, f)
    VALUES ('Row #1' , NULL , NULL , NULL   , NULL      ),
           ('Row #2' , ''   , ''   , 123    , '20160201'),
           ('Row #3' , 'ab' , 'cd' , 123.45 , '20160201');

SELECT *
FROM mytable;

BULK EXPORT mytable TO 'mytable_content.txt' WITH ( FIELDTERMINATOR = '|' );

PRINT 'Truncate mytable';
TRUNCATE TABLE mytable;

BULK INSERT mytable FROM 'mytable_content.txt' WITH ( FIELDTERMINATOR = '|', NEWIDENTITY, KEEPNULLS );

SELECT *
FROM mytable;

The result is:

a          |b       |c     |d     |e              |f                      |
-----------+--------+------+------+---------------+-----------------------+
          1|Row #1  |<NULL>|<NULL>|         <NULL>|                 <NULL>|
          2|Row #2  |      |······|         123.00|2016-02-01 00:00:00.000|
          3|Row #3  |ab    |cd····|         123.45|2016-02-01 00:00:00.000|

(3 row(s) affected)

(3 row(s) affected)

Truncate mytable
(3 row(s) affected)

(3 row(s) affected)

a          |b       |c     |d     |e              |f                      |
-----------+--------+------+------+---------------+-----------------------+
          4|Row #1  |<NULL>|<NULL>|         <NULL>|                 <NULL>|
          5|Row #2  |      |······|         123.00|2016-02-01 00:00:00.000|
          6|Row #3  |ab    |cd····|         123.45|2016-02-01 00:00:00.000|

(3 row(s) affected)

The file ‘mytable_content.txt’ is:

1|Row #1||||
2|Row #2|\0|      |123.00|20160201 00:00:00
3|Row #3|ab|cd    |123.45|20160201 00:00:00

On Row #2, \0 is in fact the byte 0x00.
RSQL, a simple alternative to Microsoft SQL Server