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 that char 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'.

TIME_FORMAT = ‘time_format’

specifies the output format for time columns.

Default is 'HH:mm:ss.FFFFFFFFF'.

DATETIME_FORMAT = ‘datetime_format’

specifies the output format for datetime columns.

Default is 'yyyyMMdd HH:mm:ss.FFFFFFFFF'.

Remarks

This statement doesn’t exist in MS SQL Server.

Examples

See BULK INSERT.

Guide