Массовая вставка со столбцом identity (auto-increment)

Я пытаюсь добавить данные в базу данных из файла CSV.

таблица сотрудников имеет столбец ID (PK) автоматическое увеличение.

CREATE TABLE [dbo].[Employee](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [Address] [varchar](50) NULL
) ON [PRIMARY]

Я использую этот запрос:

BULK INSERT Employee  FROM 'pathtempFile.csv ' 
WITH (FIRSTROW = 2,KEEPIDENTITY,FIELDTERMINATOR = ',' , ROWTERMINATOR = 'n');

.CSV файл -

Name,Address
name1,addr test 1
name2,addr test 2

но это приводит к этому сообщению об ошибке:

ошибка преобразования данных массовой загрузки (несоответствие типа или недопустимый символ для указанной кодовой страницы) для строки 2, столбца 1 (id).

7 54
sql-server bulkinsert identity-column

7 ответов:

не навалом вставить в ваш реальные таблицы напрямую.

Я всегда

  1. вставить в постановка таблица dbo.Employee_Staging (без IDENTITY столбец) из файла CSV
  2. возможно редактировать / очищать / манипулировать импортированными данными

  3. а затем скопируйте данные в реальную таблицу с помощью оператора T-SQL, например:

    INSERT INTO dbo.Employee(Name, Address) 
   SELECT Name, Address
   FROM dbo.Employee_Staging
35

добавьте столбец id в csv-файл и оставьте его пустым:

id,Name,Address
,name1,addr test 1
,name2,addr test 2

удалить ключевое слово KEEPIDENTITY из запроса:

BULK INSERT Employee  FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

поле id identity будет автоматически увеличено.

Если вы назначаете значения полю id в csv, они будут игнорироваться, если вы не используете ключевое слово KEEPIDENTITY, то они будут использоваться вместо автоматического приращения.

85

у меня была аналогичная проблема, но мне нужно было убедиться, что порядок идентификатора совпадает с порядком в исходном файле. Мое решение использует представление для массовой вставки:

сохраните таблицу как есть и создайте это представление (выберите все, кроме столбца ID)

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT [Name], [Address]
FROM [dbo].[Employee];

ваша объемная вставка должна выглядеть так:

BULK INSERT [dbo].[VW_Employee] FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');
22

вы должны сделать массовую вставку с файлом формата:

   BULK INSERT Employee FROM 'path\tempFile.csv ' 
   WITH (FORMATFILE = 'path\tempFile.fmt');

где формат файла (tempFile.дрм) выглядит так:

11.0
2
1 SQLCHAR 0 50 "\t " 2 имя SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\r\n " 3 Адрес SQL_Latin1_General_CP1_CI_AS

подробнее здесь -http://msdn.microsoft.com/en-us/library/ms179250.aspx

6

мое решение состоит в том, чтобы добавить поле ID в качестве последнего поля в таблице, поэтому bulk insert игнорирует его и получает автоматические значения. Чисто и просто ...

например, если вставить во временную таблицу:

CREATE TABLE #TempTable 
(field1 varchar(max), field2 varchar(max), ... 
ROW_ID int IDENTITY(1,1) NOT NULL)

отметим, что ROW_ID поле всегда должно быть указано как последнее поле!

2

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

Так что ваш SQL делает что-то вроде этого:

  1. если временная таблица существует, drop
  2. создать временную таблицу
  3. массовый импорт во временную таблицу
  4. Alter temp table add identity
  6. Drop temp table

по-прежнему не очень чистый, но это другой вариант... возможно, придется получить замки, чтобы быть в безопасности, тоже.

0

у меня была точно такая же проблема, которая привела к потере часов, поэтому я вдохновлен поделиться своими выводами и решениями, которые работали на меня.

1. Используйте файл excel

Это подход, который я принял. Вместо того, чтобы использовать файл csv, я использовал файл excel (.XLSX) с содержанием, как показано ниже.

id  username   email                token website

    johndoe   johndoe@divostar.com        divostar.com
    bobstone  bobstone@divosays.com        divosays.com

обратите внимание, что столбец id не имеет значения.

затем подключитесь к своей БД с помощью Microsoft SQL Server Management Studio и щелкните правой кнопкой мыши база данных и выберите Импорт данных (подменю в разделе задача). Выберите Microsoft Excel в качестве источника. Когда Вы дойдете до этапа под названием "Выбор исходных таблиц и представлений", нажмите изменить сопоставления. Ибо id столбец под пунктом назначения, нажмите на него и выберите игнорировать . Не проверяйте Enable Identity insert если вы не хотите, чтобы mantain ids incases, где вы импортируете данные из другой базы данных, и хотели бы сохранить идентификатор автоматического приращения исходной БД. Приступайте к финишу и все. Ваш данные будут импортированы плавно.

2. Использование CSV-файла

в вашем csv-файле убедитесь, что ваши данные, как показано ниже.

id,username,email,token,website
,johndoe,johndoe@divostar.com,,divostar.com
,bobstone,bobstone@divosays.com,,divosays.com

выполнить запрос ниже:

BULK INSERT Metrics FROM 'D:\Data Management\Data\CSV2\Production Data 2004 - 2016.csv '
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

проблема с этим подходом заключается в том, что CSV должен быть на сервере БД или в какой-то общей папке, к которой БД может иметь доступ, иначе вы можете получить ошибку типа "не удается открыть файл. Операционная система вернула код ошибки 21 (устройство не работает готовый.")

если вы подключаетесь к удаленной базе данных, то вы можете загрузить свой CSV в каталог на этом сервере и ссылаться на путь в bulk insert.

3. Использование CSV-файла и параметра импорта Microsoft SQL Server Management Studio

запустите импорт данных, как в первом подходе. В поле Источник выберите источник "плоский файл" и найдите свой CSV-файл. Убедитесь, что правое меню (Общие, столбцы, дополнительно, предварительный просмотр) в порядке. Обязательно установите правый разделитель в меню столбцы (column delimiter). Как и в подходе excel выше, нажмите изменить сопоставления. Для столбца id в разделе назначение нажмите на него и выберите игнорировать .

продолжайте, чтобы закончить, и это все. Ваши данные будут импортированы плавно.

0