Use native format to import or export data (SQL Server)
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:
Bulk exporting the data from a source table into a data file
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.
-
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;
Related tasks
To use data formats for bulk import or bulk export
Import Native and Character Format Data from Earlier Versions of SQL Server
Use Unicode Character Format to Import or Export Data (SQL Server)
Use Unicode Native Format to Import or Export Data (SQL Server)
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)
Atsauksmes
https://aka.ms/ContentUserFeedback.
Drīzumā: 2024. gada laikā mēs pakāpeniski pārtrauksim izmantot “GitHub problēmas” kā atsauksmju par saturu mehānismu un aizstāsim to ar jaunu atsauksmju sistēmu. Papildinformāciju skatiet:Iesniegt un skatīt atsauksmes par