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 forchar
. - 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.
- If KEEPIDENTITY is not specified, the value of IDENTITY column in text file are ignored. The field in the text file can be empty.
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
orchar
datatypes, instead or keeping the spaces. - This is not a valid MS SQL Server option.
- This is useful if you have a text file with fixed-length columns, and want to convert fields with only spaces into NULL for
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.