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.

See also BULK INSERT.

Examples

See BULK INSERT.

RSQL, a simple alternative to Microsoft SQL Server