Skip to content

keep-identity-values-when-bulk-importing-data-sql-server.md

Latest commit

 

History

History
329 lines (257 loc) · 17.5 KB

keep-identity-values-when-bulk-importing-data-sql-server.md

File metadata and controls

329 lines (257 loc) · 17.5 KB
title Keep Identity Values When Bulk Importing Data (SQL Server) | Microsoft Docs
ms.custom
ms.date 09/21/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-bulk-import-export
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
identity values [SQL Server], bulk imports
data formats [SQL Server], identity values
bulk importing [SQL Server], identity values
ms.assetid 45894a3f-2d8a-4edd-9568-afa7d0d3061f
caps.latest.revision 22
author JennieHubbard
ms.author jhubbard
manager jhubbard
ms.workload On Demand

Keep Identity Values When Bulk Importing Data (SQL Server)

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 [!INCLUDEssNoVersion] 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.

Outline
Keep Identity Values
Example Test Conditions
 ● Sample Table
 ● Sample Data File
 ● Sample Non-XML Format File
Examples
 ● Using bcp and Keeping Identity Values without a Format File
 ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● Using bcp and Generated Identity Values without a Format File
 ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● Using BULK INSERT and Generated Identity Values without a Format File
 ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

Keep Identity Values

To prevent [!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] 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.

Example Test Conditions

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

Sample Table

The script below creates a test database and a table named myIdentity. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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
   );

Sample Data File

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;

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, 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

Examples

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

Using bcp and Keeping Identity Values without a Format File

-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;"

Using bcp and Keeping Identity Values with a Non-XML Format File

-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 bcp and Generated Identity Values without a Format File

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 bcp and Generated Identity Values with a Non-XML Format File

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;"

Using BULK INSERT and Keeping Identity Values without a Format File

KEEPIDENTITY argument. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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;

Using BULK INSERT and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY and the FORMATFILE argument. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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 BULK INSERT and Generated Identity Values without a Format File

Using defaults. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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 BULK INSERT and Generated Identity Values with a Non-XML Format File

Using defaults and FORMATFILE argument. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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;

Using OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY table hint and FORMATFILE argument. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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 OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

Using defaults and FORMATFILE argument. Execute the following Transact-SQL in Microsoft [!INCLUDEssManStudioFull] (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;

Related Tasks

To use a format file

To use data formats for bulk import or bulk export

To specify data formats for compatibility when using bcp

  1. Specify Field and Row Terminators (SQL Server)

  2. Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. Specify File Storage Type by Using bcp (SQL Server)

See Also

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)