Поделиться через

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

  • Статья

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

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

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

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

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

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

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

Дополнительные сведения см. в статье bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL),SELECT (Transact-SQL), and Table Hints (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 Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Подсказки таблиц (Transact-SQL)
Файлы форматирования для импорта или экспорта данных (SQL Server)