BULK INSERT с идентификатором (автоинкремент)

Ответ 1

Не вставляйте BULK INSERT в свои реальные таблицы.

Я всегда

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

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

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

Ответ 2

Добавьте столбец 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 в csv, они будут игнорироваться, если вы не используете ключевое слово KEEPIDENTITY, тогда они будут использоваться вместо автоматического увеличения.

Ответ 3

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

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

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

Ваш BULK INSERT должен выглядеть следующим образом:

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

Ответ 4

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

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

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

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

Ответ 5

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

Например, если вставить в таблицу temp:

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

Обратите внимание, что поле ROW_ID ДОЛЖНО всегда указываться как поле LAST!

Ответ 6

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

Итак, ваш sql делает что-то вроде этого:

• Если существует временная таблица, нажмите
• Создать временную таблицу
• Массовый импорт в временную таблицу
• Изменять таблицу temp add identity
• < что бы вы ни делали с данными >
• Таблица временных темпов

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

Ответ 7

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

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

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

id  username   email                token website

    johndoe   [email protected]        divostar.com
    bobstone  [email protected]        divosays.com

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

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

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

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

id,username,email,token,website
,johndoe,[email protected],,divostar.com
,bobstone,[email protected],,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 в каталог на этом сервере и указать путь в массовой вставке.

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

Запустите данные импорта, как в первом подходе. Для источника выберите Плоский файл Source и перейдите к файлу CSV. Убедитесь, что в правом меню (Общие, Столбцы, Дополнительно, Предварительный просмотр) все в порядке. Не забудьте установить правильный разделитель в меню столбцов (разделитель столбцов). Как и в предыдущем примере, нажмите изменить сопоставления. Для столбца id под пунктом назначения щелкните по нему и выберите игнорировать.

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

Ответ 8

1. Создать таблицу со столбцом Identity + другие столбцы;
2. Создайте вид и выставьте только те столбцы, которые вы будете вставлять навалом;
3. ППГ в поле зрения

Ответ 9

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

Я решил это, используя OPENROWSET вариант BULK INSERT. Он использует тот же файл формата и работает таким же образом, но он позволяет читать файл данных с помощью инструкции SELECT.

Создайте свой стол:

CREATE TABLE target_table(
id bigint IDENTITY(1,1),
col1 varchar(256) NULL,
col2 varchar(256) NULL,
col3 varchar(256) NULL)

Откройте окно командной строки:

bcp dbname.dbo.target_table format nul -c -x -f C:\format_file.xml -t; -T

Это создает файл формата на основе того, как выглядит таблица.

Теперь отредактируйте файл формата и удалите все строки, где FIELD ID = "1" и COLUMN SOURCE = "1", поскольку в нашем файле данных этого нет.
Также настройте терминаторы, которые могут потребоваться для вашего файла данных:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="col1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="col2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="col3" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

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

INSERT INTO target_table (col1,col2, col3)
SELECT * FROM  openrowset(
bulk 'C:\data_file.txt',
formatfile='C:\format_file.xml') as t;