Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. By default, the values for the identity column in the data file that is imported are ignored and SQL Server assigns unique values automatically. The unique values are based on the seed and increment values that are specified during table creation.
If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. See Use a Format File to Skip a Table Column (SQL Server) for additional information.
To prevent SQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. When you specify a keep-identity qualifier, SQL Server uses the identity values in the data file. These qualifiers are as follows:
Command | Keep-identity qualifier | Qualifier type |
---|---|---|
bcp | -E | Switch |
BULK INSERT | KEEPIDENTITY | Argument |
INSERT ... SELECT * FROM OPENROWSET(BULK...) | KEEPIDENTITY | Table hint |
For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).
Note
To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.
The examples in this topic are based on the table, data file, and format file defined below.
The script below creates a test database and a table named myIdentity
. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.myIdentity (
PersonID smallint IDENTITY(1,1) NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate date
);
Using Notepad, create an empty file D:\BCP\myIdentity.bcp
and insert the data below.
3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05
Alternatively, you can execute the following PowerShell script to create and populate the data file:
cls
# revise directory as desired
$dir = 'D:\BCP\';
$bcpFile = $dir + 'myIdentity.bcp';
# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
Write-Host "The path $dir does not exist; please create or modify the directory.";
RETURN;
};
# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;
# Add data
Add-Content -Path $bcpFile -Value '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';
#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;
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, myIdentity.fmt
, based on the schema of myIdentity
. 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, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:
bcp TestDatabase.dbo.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T
REM Review file
Notepad D:\BCP\myIdentity.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
The examples below use the database, datafile, and format files created above.
-E switch. At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"
REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"
-E and -f switches. At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"
REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"
Using defaults. At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"
REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"
Using defaults and -f switch. At a command prompt, enter the following command:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"
REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"
KEEPIDENTITY argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
FROM 'D:\BCP\myIdentity.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPIDENTITY
);
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
KEEPIDENTITY and the FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
FROM 'D:\BCP\myIdentity.bcp'
WITH (
FORMATFILE = 'D:\BCP\myIdentity.fmt',
KEEPIDENTITY
);
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
Using defaults. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
FROM 'D:\BCP\myIdentity.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
Using defaults and FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
FROM 'D:\BCP\myIdentity.bcp'
WITH (
FORMATFILE = 'D:\BCP\myIdentity.fmt'
);
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
KEEPIDENTITY table hint and FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY)
(PersonID, FirstName, LastName, BirthDate)
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myIdentity.bcp',
FORMATFILE = 'D:\BCP\myIdentity.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
Using defaults and FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myIdentity; -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
SELECT FirstName, LastName, BirthDate
FROM OPENROWSET (
BULK 'D:\BCP\myIdentity.bcp',
FORMATFILE = 'D:\BCP\myIdentity.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;
To use a format file
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)
To specify data formats for compatibility when using bcp
BACKUP (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Table Hints (Transact-SQL)
Format Files for Importing or Exporting Data (SQL Server)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Explore data manipulation options in Azure SQL Database - Training
Learn how to invoke REST endpoints in Azure SQL Database and manipulate data using Azure Functions. Also, explore various tools and options for importing and exporting data to and from Azure SQL Database.
Documentation
Keep nulls or default values during bulk import - SQL Server
For bulk import in SQL Server, both bcp and BULK INSERT load default values to replace null values. For both, you can choose to retain null values.
BULK INSERT (Transact-SQL) - SQL Server
Transact-SQL reference for the BULK INSERT statement.
Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server - SQL Server
Find out how to use Transact-SQL statements to bulk import data from a file to a SQL Server or Azure SQL Database table, including security considerations.