Muokkaa

Jaa

Use native format to import or export data (SQL Server)

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Native format is recommended when you bulk transfer data between multiple instances of SQL Server using a data file that does not contain any extended/double-byte character set (DBCS) characters.

Note

To bulk transfer data between multiple instances of SQL Server by using a data file that contains extended or DBCS characters, you should use the Unicode native format. For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).

Native format maintains the native data types of a database. Native format is intended for high-speed data transfer of data between SQL Server tables. If you use a format file, the source and target tables do not need to be identical. The data transfer involves two steps:

  1. Bulk exporting the data from a source table into a data file

  2. Bulk importing the data from the data file into the target table

The use of native format between identical tables avoids unnecessary conversion of data types to and from character format, saving time and space. To achieve the optimum transfer rate, however, few checks are performed regarding data formatting. To prevent problems with the loaded data, see the following restrictions list.

Restrictions

To import data in native format successfully, ensure that:

  • The data file is in native format.

  • Either the target table must be compatible with the data file (having the correct number of columns, data type, length, NULL status, and so forth), or you must use a format file to map each field to its corresponding columns.

    Note

    If you import data from a file that is mismatched with the target table, the import operation might succeed but the data values inserted into the target table are likely to be incorrect. This is because the data from the file is interpreted by using the format of the target table. Therefore, any mismatch results in the insertion of incorrect values. However, under no circumstances can such a mismatch cause logical or physical inconsistencies in the database.

    For information on using format files, see Format Files for Importing or Exporting Data (SQL Server).

A successful import will not corrupt the target table.

How bcp handles data in native format

This section discusses special considerations for how the bcp utility exports and imports data in native format.

  • Noncharacter data

    The bcp utility uses the SQL Server internal binary data format to write noncharacter data from a table to a data file.

  • char or varchar data

    At the beginning of each char or varchar field, bcp adds the prefix length.

    Important

    When native mode is used, by default, the bcp utility converts characters from SQL Server to OEM characters before it copies them to a data file. The bcp utility converts characters from a data file to ANSI characters before it bulk imports them into a SQL Server table. During these conversions, extended character data can be lost. For extended characters, either use Unicode native format or specify a code page.

  • sql_variant data

    If sql_variant data is stored as a SQLVARIANT in a native-format data file, the data maintains all of its characteristics. The metadata that records the data type of each data value is stored along with the data value. This metadata is used to re-create the data value with the same data type in a destination sql_variant column.

    If the data type of the destination column is not sql_variant, each data value is converted to the data type of the destination column, following the normal rules of implicit data conversion. If an error occurs during data conversion, the current batch is rolled back. Any char and varchar values that are transferred between sql_variant columns may have code page conversion issues.

    For more information about data conversion, see Data Type Conversion (Database Engine).

Command options for native format

You can import native format data into a table using bcp, BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...). For a bcp command or BULK INSERT statement, you can specify the data format in the statement. For an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, you must specify the data format in a format file.

Native format is supported by the following command options:

Command Option Description
bcp -n Causes the bcp utility to use the native data types of the data.*
BULK INSERT DATAFILETYPE ='native' Uses the native or wide native data types of the data. Note that DATAFILETYPE is not needed if a format file specifies the data types.
OPENROWSET N/A Must use a format file

*To load native (-n) data to a format compatible with earlier versions of SQL Server clients, use the -V switch. For more information, see Import Native and Character Format Data from Earlier Versions of SQL Server.

Note

Alternatively, you can specify formatting on a per-field basis in a format file. For more information, see Format Files for Importing or Exporting Data (SQL Server).

Example test conditions

The examples in this topic are based on the table, and format file defined below.

Sample table

The script below creates a test database, a table named myNative and populates the table with some initial values. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myNative;

Sample non-XML format file

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myNative.fmt, based on the schema of myNative. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following commands:

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T 

REM Review file
Notepad D:\BCP\myNative.fmt

Important

Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Examples

The examples below use the database, and format files created above.

Using bcp and native format to export data

-n switch and OUT command. Note: the data file created in this example will be used in all subsequent examples. At a command prompt, enter the following commands:

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

Using bcp and native format to import data without a format file

-n switch and IN command. At a command prompt, enter the following commands:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Using bcp and native format to import data with a non-XML format file

-n and -f switches and IN command. At a command prompt, enter the following commands:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Using BULK INSERT and native format without a format file

DATAFILETYPE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
	FROM 'D:\BCP\myNative.bcp'
	WITH (
		DATAFILETYPE = 'native'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

Using BULK INSERT and native format with a non-XML format file

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
   FROM 'D:\BCP\myNative.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNative.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

Using OPENROWSET and native format with a non-XML format file

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative;  -- for testing
INSERT INTO TestDatabase.dbo.myNative
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNative.bcp', 
		FORMATFILE = 'D:\BCP\myNative.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNative;

To use data formats for bulk import or bulk export

See also

bcp Utility
BULK INSERT (Transact-SQL)
Data Types (Transact-SQL)
sql_variant (Transact-SQL)
Import Native and Character Format Data from Earlier Versions of SQL Server
OPENROWSET (Transact-SQL)
Use Unicode Native Format to Import or Export Data (SQL Server)