Сохранение значений идентификаторов при массовом импорте данных (SQL Server)Keep identity values when bulk importing data (SQL Server)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Для файлов данных, содержащих значения идентификаторов, можно выполнить массовый импорт в экземпляр Microsoft SQL Server.Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. По умолчанию значения столбца идентификаторов в импортируемом файле данных не учитываются, и SQL ServerSQL Server автоматически присваивает им уникальные значенияBy default, the values for the identity column in the data file that is imported are ignored and SQL ServerSQL 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. Дополнительные сведения см. в статье Пропуск столбца таблицы с помощью файла форматирования (SQL Server) .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
 ● Образец файла форматирования в формате, отличном от XML ● Sample Non-XML Format File
ПримерыExamples
 ● Использование команды bcp и сохранение значений идентификаторов без файла форматирования ● Using bcp and Keeping Identity Values without a Format File
 ● Использование команды bcp и сохранение значений идентификаторов с файлом форматирования ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● Использование команды bcp и созданных значений идентификаторов без файла форматирования ● Using bcp and Generated Identity Values without a Format File
 ● Использование команды bcp и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● Использование инструкции BULK INSERT и сохранение значений идентификаторов без файла форматирования ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● Использование инструкции BULK INSERT и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● Использование инструкции BULK INSERT и созданных значений идентификаторов без файла форматирования ● Using BULK INSERT and Generated Identity Values without a Format File
 ● Использование инструкции BULK INSERT и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● Использование инструкции OPENROWSET и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● Использование инструкции OPENROWSET и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

Сохранение значений идентификаторов Keep Identity Values

Чтобы SQL ServerSQL Server не присваивал значения идентификаторов при массовом импорте строк в таблицу, используется соответствующий квалификатор команды для сохранения значений идентификаторов.To prevent SQL ServerSQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. При указании квалификатора сохранения значений идентификаторов SQL ServerSQL Server пользуется значениями идентификаторов, имеющимися в файле данных.When you specify a keep-identity qualifier, SQL ServerSQL Server uses the identity values in the data file. Ниже приведены эти квалификаторы.These qualifiers are as follows:

CommandCommand Квалификатор сохранения значений идентификаторовKeep-identity qualifier Тип квалификатораQualifier type
bcpbcp -E-E ПараметрSwitch
BULK INSERTBULK INSERT KEEPIDENTITYKEEPIDENTITY АргументArgument
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...).INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITYKEEPIDENTITY Табличное указаниеTable hint

Дополнительные сведения см. в статьях Программа bcp, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) и Табличные указания (Transact-SQL).For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).

Примечание

Сведения о том, как создать автоматически увеличивающееся числовое значение, которое может использоваться в нескольких таблицах или вызываться из приложений без ссылки на какие-либо таблицы, см. в разделе Порядковые номера.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

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myIdentity.The script below creates a test database and a table named myIdentity. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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
   );

Образец файла данныхSample Data File

С помощью Блокнота создайте пустой файл D:\BCP\myIdentity.bcp и вставьте данные, приведенные ниже.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

Кроме того, можно выполнить следующий сценарий PowerShell для создания и заполнения файла данных: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;

Образец файла форматирования в формате, отличном от XMLSample Non-XML Format File

SQL Server поддерживает два типа файлов форматирования: файлы форматирования в формате, отличном от XML, и XML-файлы форматирования.SQL Server support two types of format file: non-XML format and XML format. Файл форматирования не в формате XML поддерживается более ранними версиями SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Дополнительные сведения см. в разделе Файлы формата, отличные от XML (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Следующая команда будет использовать служебную программу bcp для создания файла форматирования myIdentity.fmtв формате, отличном от XML, на основе схемы myIdentity.The following command will use the bcp utility to generate a non-xml format file, myIdentity.fmt, based on the schema of myIdentity. Чтобы создать файл форматирования с помощью служебной программы bcp , укажите аргумент format , а вместо пути файла данных задайте значение nul .To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. Параметр format также требует наличия параметра -f .The format option also requires the -f option. Кроме того, в этом примере квалификатор c используется для указания символьных данных, t, используется для указания запятой в качестве признака конца поля, а T используется для указания доверенного подключения с использованием встроенной системы безопасности.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

Важно!

Убедитесь, что файл форматирования не в формате XML заканчивается символом перевода строки или возврата каретки.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.

Использование команды bcp и сохранение значений идентификаторов без файла форматированияUsing bcp and Keeping Identity Values without a Format File

Параметр -E .-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;"

Использование команды bcp и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using bcp and Keeping Identity Values with a Non-XML Format File

Параметры -E и -f .-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;"

Использование команды bcp и созданных значений идентификаторов без файла форматирования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;"

Использование команды bcp и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using bcp and Generated Identity Values with a Non-XML Format File

Использование значений по умолчанию и параметра -f .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;"

Использование инструкции BULK INSERT и сохранение значений идентификаторов без файла форматированияUsing BULK INSERT and Keeping Identity Values without a Format File

АргументKEEPIDENTITY .KEEPIDENTITY argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Использование инструкции BULK INSERT и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using BULK INSERT and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY и аргумент FORMATFILE .KEEPIDENTITY and the FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Использование инструкции BULK INSERT и созданных значений идентификаторов без файла форматированияUsing BULK INSERT and Generated Identity Values without a Format File

Использование значений по умолчанию.Using defaults. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Использование инструкции BULK INSERT и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using BULK INSERT and Generated Identity Values with a Non-XML Format File

Использование значений по умолчанию и аргумента FORMATFILE .Using defaults and FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Использование инструкции OPENROWSET(BULK...) и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

Табличное указаниеKEEPIDENTITY и аргумент FORMATFILE .KEEPIDENTITY table hint and FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Использование инструкции OPENROWSET(BULK...) и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML Using OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

Использование значений по умолчанию и аргумента FORMATFILE .Using defaults and FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Связанные задачиRelated Tasks

Использование файла форматированияTo use a format file

Использование форматов данных для массового импорта или экспортаTo use data formats for bulk import or bulk export

Задание форматов данных для совместимости с помощью программы bcpTo specify data formats for compatibility when using bcp

  1. Определение признаков конца поля и строки (SQL Server)Specify Field and Row Terminators (SQL Server)

  2. Определение длины префикса в файлах данных с помощью программы bcp (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. Указание типа файлового хранилища с помощью программы bcp (SQL Server)Specify File Storage Type by Using bcp (SQL Server)

См. также:See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Табличные указания (Transact-SQL)Table Hints (Transact-SQL)
Файлы форматирования для импорта или экспорта данных (SQL Server)Format Files for Importing or Exporting Data (SQL Server)