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

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема аналитической платформы (PDW)

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

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

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

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

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

Эти квалификаторы следующие:

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

Дополнительные сведения см. в статье bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (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 вместо пути к файлу данных.
• Для параметра форматирования также требуется параметр -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 и сохраняйте значения Identity с форматом файла, отличным от 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;

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

• Сохранение значений NULL или значений по умолчанию во время массового импорта (SQL Server)

• Подготовка данных для массового экспорта или импорта

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

• Создание файла форматирования с помощью bcp (SQL Server)

• Использование файла форматирования для массового импорта данных (SQL Server)

• Использование файла форматирования для сопоставления столбцов таблиц с полями файлов данных (SQL Server)

• Использование файла форматирования для пропуска поля данных (SQL Server)

• Использование файла форматирования для пропуска столбца таблицы (SQL Server)

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

• Импорт данных собственного и символьного формата из более ранних версий SQL Server

• Использование формата символов для импорта или экспорта данных (SQL Server)

• Использование собственного формата для импорта или экспорта данных (SQL Server)

• Использование формата символов Юникода для импорта или экспорта данных (SQL Server)

• Использование собственного формата Юникода для импорта или экспорта данных (SQL Server)

Задание форматов данных для совместимости с помощью программы 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)