Сохранение значений идентификаторов при массовом импорте данных (SQL Server)

  • Статья

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

Для файлов данных, содержащих значения идентификаторов, можно выполнить массовый импорт в экземпляр Microsoft SQL Server. По умолчанию значения столбца идентификаторов в импортируемом файле данных не учитываются, и SQL Server автоматически присваивает им уникальные значения на основе начального значения и значения приращения, указанных при создании таблицы.

Если файл данных не содержит значений для столбцов идентификаторов в таблице, то для указания того, что при импорте столбец идентификаторов в таблице нужно пропустить, применяется файл форматирования. Дополнительные сведения см. в статье Пропуск столбца таблицы с помощью файла форматирования (SQL Server) .

Контур
Сохранение значений идентификаторов
Пример условий теста
 ● Пример таблицы
 ● Пример файла данных
 ● Пример файла форматирования в формате, отличном от XML
Примеры
 ● Использование bcp и сохранение значений идентификаторов без файла форматирования
 ● Использование bcp и сохранение значений идентификаторов в файле форматирования, отличном от XML
 ● Использование bcp и созданных значений идентификаторов без файла форматирования
 ● Использование bcp и созданных значений идентификаторов с файлом форматирования в формате, отличном от XML
 ● Использование инструкции BULK INSERT и сохранение значений идентификаторов без файла форматирования
 ● Использование инструкции BULK INSERT и сохранение значений идентификаторов в файле форматирования, отличном от XML
 ● Использование инструкции BULK INSERT и созданных значений идентификаторов без файла форматирования
 ● Использование инструкции BULK INSERT и созданных значений идентификаторов с файлом форматирования в формате, отличном от XML
 ● Использование OPENROWSET и сохранение значений идентификаторов в файле форматирования, отличном от XML
 ● Использование OPENROWSET и созданных значений идентификаторов с файлом форматирования в формате, отличном от XML

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

Чтобы SQL Server не присваивал значения идентификаторов при массовом импорте строк в таблицу, используется соответствующий квалификатор команды для сохранения значений идентификаторов. При указании квалификатора сохранения значений идентификаторов SQL Server пользуется значениями идентификаторов, имеющимися в файле данных. Ниже приведены эти квалификаторы.

Get-Help Квалификатор сохранения значений идентификаторов Тип квалификатора
bcp -E Параметр
BULK INSERT KEEPIDENTITY Аргумент
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...). KEEPIDENTITY Табличное указание

Дополнительные сведения см. в разделах служебная программа bcp, BULK INSERT (Transact-SQL),OPENROWSET (Transact-SQL),INSERT (Transact-SQL),SELECT (Transact-SQL) и Табличные указания (Transact-SQL).

Примечание

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

Пример условий теста

Примеры в этом разделе основаны на таблице, файле данных и файле форматирования, которые определены ниже.

Образец таблицы

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myIdentity. Выполните следующий запрос Transact-SQL в 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
   );

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

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

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 для создания и заполнения файла данных:

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;

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

SQL Server поддерживает два типа файлов форматирования: файлы форматирования в формате, отличном от XML, и XML-файлы форматирования. Файл форматирования не в формате XML поддерживается более ранними версиями SQL Server. Дополнительные сведения см. в разделе Файлы формата, отличные от XML (SQL Server) . Следующая команда будет использовать служебную программу bcp для создания файла форматирования myIdentity.fmtв формате, отличном от XML, на основе схемы myIdentity. Чтобы создать файл форматирования с помощью служебной программы bcp , укажите аргумент format , а вместо пути файла данных задайте значение nul . Параметр format также требует наличия параметра -f . Кроме того, в этом примере квалификатор c используется для указания символьных данных, t, используется для указания запятой в качестве признака конца поля, а T используется для указания доверенного подключения с использованием встроенной системы безопасности. В командной строке введите следующую команду:

bcp TestDatabase.dbo.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

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

Важно!

Убедитесь, что файл форматирования не в формате XML заканчивается символом перевода строки или возврата каретки. В противном случае, скорее всего, появится следующее сообщение об ошибке:

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

Примеры

В приведенных ниже примерах используется база данных, файл данных и файлы форматирования, созданные ранее.

Использование команды bcp и сохранение значений идентификаторов без файла форматирования

Параметр -E . В командной строке введите следующую команду:

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

Параметры -E и -f . В командной строке введите следующую команду:

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

Использование значений по умолчанию. В командной строке введите следующую команду:

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

Использование значений по умолчанию и параметра -f . В командной строке введите следующую команду:

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

АргументKEEPIDENTITY . Выполните следующий запрос Transact-SQL в 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;

Использование инструкции BULK INSERT и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML

KEEPIDENTITY и аргумент FORMATFILE . Выполните следующий запрос Transact-SQL в 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;

Использование инструкции BULK INSERT и созданных значений идентификаторов без файла форматирования

Использование значений по умолчанию. Выполните следующий запрос Transact-SQL в 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;

Использование инструкции BULK INSERT и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML

Использование значений по умолчанию и аргумента FORMATFILE . Выполните следующий запрос Transact-SQL в 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;

Использование инструкции OPENROWSET(BULK...) и сохранение значений идентификаторов с помощью файла форматирования в формате, отличном от XML

Табличное указаниеKEEPIDENTITY и аргумент FORMATFILE . Выполните следующий запрос Transact-SQL в 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;

Использование инструкции OPENROWSET(BULK...) и созданных значений идентификаторов с помощью файла форматирования в формате, отличном от XML

Использование значений по умолчанию и аргумента FORMATFILE . Выполните следующий запрос Transact-SQL в 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;

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

Использование файла форматирования

Использование форматов данных для массового импорта или экспорта

Задание форматов данных для совместимости с помощью программы bcp

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

  2. Определение длины префикса в файлах данных с помощью программы bcp (SQL Server)

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

См. также:

BACKUP (Transact-SQL)
Программа bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Табличные указания (Transact-SQL)
Файлы форматирования для импорта или экспорта данных (SQL Server)