Для файлов данных, содержащих значения идентификаторов, можно выполнить массовый импорт в экземпляр Microsoft SQL Server. По умолчанию значения столбца удостоверений в импортируемом файле данных игнорируются, а SQL Server автоматически назначает уникальные значения. Уникальные значения основаны на начальном значении и значении приращения, указанных при создании таблицы.
Если файл данных не содержит значений для столбцов идентификаторов в таблице, то для указания того, что при импорте столбец идентификаторов в таблице нужно пропустить, применяется файл форматирования. Дополнительные сведения см. в статье Пропуск столбца таблицы с помощью файла форматирования (SQL Server) .
Чтобы предотвратить назначение SQL Server идентификаторов при массовом импорте строк данных в таблицу, используйте соответствующий квалификатор команды keep-identity. При указании квалификатора сохранения идентификатора SQL Server использует значения идентификаторов в файле данных. Эти квалификаторы следующие:
Сведения о том, как создать автоматически увеличивающееся числовое значение, которое может использоваться в нескольких таблицах или вызываться из приложений без ссылки на какие-либо таблицы, см. в разделе Порядковые номера.
Пример условий теста
Примеры в этом разделе основаны на таблице, файле данных и файле форматирования, которые определены ниже.
Образец таблицы
Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем 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 и вставьте данные, приведенные ниже.
Кроме того, можно выполнить следующий сценарий 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;"
Ключи -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;"
Использование значений по умолчанию и параметра -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;
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;
Использование значений по умолчанию и аргумента 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;
Табличное указание 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;
При массовом импорте в SQL Server как bcp, так и BULK INSERT загружают значения по умолчанию для замены значений NULL. Для обоих вариантов можно выбрать сохранение значений NULL.
Сведения о том, как использовать инструкции Transact-SQL для массового импорта данных из файла в таблицу Базы данных SQL Azure или SQL Server, включая аспекты безопасности.
В SQL Server можно использовать файл форматирования в операциях массового импорта. Файл форматирования сопоставляет поля в файле данных столбцам таблицы.
В этой статье описывается планирование операций массового импорта и массового экспорта, включая требования к формату файлов данных, а также использование служебной программы bcp.
При массовом импорте или экспорте таблицы SQL Server файл форматирования позволяет записывать файлы данных с небольшим редактированием или считывать файлы данных из других программ.
Узнайте, как вызывать конечные точки REST в База данных SQL Azure и управлять данными с помощью Функции Azure. Кроме того, изучите различные средства и параметры импорта и экспорта данных в База данных SQL Azure.
