BULK EXPORT (TSQL Statement)
exports records from a specified table into a text file.
Syntax
BULK EXPORT { <table_qname> | <select_xtable> } TO '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'
| DATE_FORMAT = 'date_format'
| TIME_FORMAT = 'time_format'
| DATETIME_FORMAT = 'datetime_format'
Arguments
- <table_qname>
- is the qualified or unqualified name of the table which records will be exported to the text file.
- <select_xtable>
- the records produced by this
SELECT
expression will be exported to the text file. - ‘data_file’
- relative or absolute path to the text file to import.
- NULL values are converted to empty fields.
- For
varchar
, empty strings are converted to character 0x00. Note thatchar
values are never empty, they always contain spaces. - 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 to write 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 to write 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'
.
DATE_FORMAT =
‘date_format’- specifies the output format for
date
columns.- Default is
'yyyyMMdd'
.
- Default is
TIME_FORMAT =
‘time_format’- specifies the output format for
time
columns.- Default is
'HH:mm:ss.FFFFFFFFF'
.
- Default is
DATETIME_FORMAT =
‘datetime_format’- specifies the output format for
datetime
columns.- Default is
'yyyyMMdd HH:mm:ss.FFFFFFFFF'
.
- Default is
Remarks
This statement doesn’t exist in MS SQL Server.
See also BULK INSERT
.
Examples
See BULK INSERT
.