Массовая вставка с файлом формата НЕ пропуская столбец в таблице назначения с 146 полями, как и должно быть

Вот полная ошибка:

Msg 4864, уровень 16, состояние 1, строка 3 Ошибка преобразования данных массовой загрузки (несоответствие типа или недопустимый символ для указанной кодовой страницы) для строки 1, столбец 5 (FK_User_CreatedBy).

И вот экзистенциальный снимок моей боли:)

Многие вопросы касаются этих проблем, но ни один из них не выполняет трюк...

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

Вот файл данных двух строк для импорта (.csv), открытый в блокноте и блокноте ++: (Да, я знаю, что ограничитель строк - это \r\n, а терминатор поля/столбца -\t или ',')

Здесь это в виде обычного текста:

1,fArty,Padul,1,10/1/1962,Head of ,Australia,AU Talavera Centre,NSW,7 CSU,[email protected]
2,mifsm,Jodel,1,10/1/1970,Chief Officer,Australia,AU ,NSW,8 CSU,[email protected]

КОНТЕКСТ/ФОН: Тест на маленькой таблице и входном файле с несколькими записями (помните, что это столбец пропущен по таблице со многими столбцами, которая заканчивается повреждением)...

Импорт РАБОТАЕТ отлично для небольшой таблицы базы данных, которая выглядит так:

И создается таким образом:

Вот код для создания таблицы:

    DROP TABLE dbo.tbl_Person_Importtest

CREATE TABLE dbo.tbl_Person_Importtest 
(
ID int PRIMARY KEY NOT NULL,
LastName varchar(100) NOT NULL, 
FirstName varchar(100) NOT NULL, 
FK_Gender varchar(4) NOT NULL, 
DateOfBirth date NOT NULL, 
JobTitle varchar(200) NOT NULL, 
Address1Country varchar(50) NOT NULL, 
Location varchar(200) NOT NULL,
Address1StateOrProvince varchar(50) NOT NULL, 
Department varchar(200) NOT NULL, 
EMailAddress1  varchar(200) NOT NULL
)

Файл формата объемной вставки .xml выглядит следующим образом:

Обратите внимание, что он также работает, если я пропускаю столбец ID (PK + index), поскольку таблица базы данных пуста, а файл импорта не имеет индекса. Это отлично работает для небольшой таблицы назначения, поскольку база данных генерирует индекс первичного ключа.

Здесь файл формата как text():

<?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="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="11"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="FK_Gender" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="DateOfBirth" xsi:type="SQLDATE"/>
  <COLUMN SOURCE="6" NAME="JobTitle" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="Address1Country" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="Location" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="Address1StateOrProvince" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="Department" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="EMailAddress1" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

И он был создан с использованием bcp в командной строке следующим образом:

Вот командная строка bcp в тексте:

bcp YFP..tbl_Person_Importtest format nul -f PersonImportMapFile.xml -c -x -T

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

Если я снова вставляю больше строк, никаких проблем...

БОЛЬШОЙ ТАБЛИЦЫ Я не могу включить полное описание из-за проблем с интеллектуальной собственностью, но в большой таблице назначения есть 146 полей без разреженных полей и много полей DATETIME и DATE, а также стеки внешние ключи (в основном INT), некоторые из которых имеют значение NULL. Вот файл карты, сгенерированный с помощью bcp (с именами полей, укороченными и некоторыми удаленными):

   CREATE TABLE [dbo].[tbl_Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordTitle] [nvarchar](250) NULL,
[SecurityCode] [nvarchar](250) NULL,
[DateCreated] [smalldatetime] NOT NULL,
[FK_User_CreatedBy] [int] NULL,
[wning] [int] NULL,
[ssigned] [int] NULL,
[ollowup] [int] NULL,
[sation_Owning] [int] NULL,
[wning] [int] NULL,
[pdate] [smalldatetime] NULL,
[astUpdate] [int] NULL,
[tatus] [bit] NULL,
[ive] [smalldatetime] NULL,
[eason] [nvarchar](250) NULL,
[tatus] [bit] NULL,
[ion] [smalldatetime] NULL,
[Titles] [int] NULL,
[LastName] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[ion] [ntext] NULL,
[Code] [nvarchar](50) NULL,
[r] [int] NULL,
[] [nvarchar](100) NULL,
[nt] [nvarchar](100) NULL,
[ame] [nvarchar](100) NULL,
[hone] [nvarchar](50) NULL,
[tName] [nvarchar](100) NULL,
[e1] [nvarchar](50) NULL,
[e2] [nvarchar](50) NULL,
[one1] [nvarchar](50) NULL,
[Moe2] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[e1] [nvarchar](250) NULL,
[e2] [nvarchar](250) NULL,
[e3] [nvarchar](250) NULL,
[Address1CityOrSuburb] [nvarchar](50) NULL,
[Address1StateOrProvince] [nvarchar](50) NULL,
[Address1Country] [nvarchar](50) NULL,
[Address1PostalCode] [nvarchar](20) NULL,
[Line1] [nvarchar](250) NULL,
[Line2] [nvarchar](250) NULL,
[Line3] [nvarchar](250) NULL,
[CityOrSuburb] [nvarchar](50) NULL,
[StateOrProvince] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[PostalCode] [nvarchar](20) NULL,
[RL] [nvarchar](200) NULL,
[ress1] [nvarchar](100) NULL,
[ress2] [nvarchar](100) NULL,
[ne] [bit] NULL,
[] [bit] NULL,
[il] [bit] NULL,
[tail] [bit] NULL,
[kEl] [bit] NULL,
[kPalMail] [bit] NULL,
[dMM] [bit] NULL,
[_Preferred] [int] NULL,
[] [int] NULL,
[onStatus] [int] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[6] [money] NULL,
[ncome] [money] NULL,
[rInc1] [money] NULL,
[rInc2] [money] NULL,
[rInc3] [money] NULL,
[rInc4] [money] NULL,
[rInc5] [money] NULL,
[rInc6] [money] NULL,
[artner] [money] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[6] [money] NULL,
[7] [money] NULL,
[8] [money] NULL,
[ud] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[] [money] NULL,
[lAss] [money] NULL,
[1] [money] NULL,
[2] [money] NULL,
[3] [money] NULL,
[4] [money] NULL,
[5] [money] NULL,
[lDebt] [money] NULL,
[rganisation_Provider] [int] NULL,
[Insurance] [money] NULL,
[ver] [money] NULL,
[itd] [nvarchar](250) NULL,
[veod] [nvarchar](250) NULL,
[fiNominated] [bit] NULL,
[ [money] NULL,
[idD] [nvarchar](50) NULL,
[ccs] [int] NULL,
[mpus] [int] NULL,
[ry] [money] NULL,
[feInsurance] [bit] NULL,
[Cor] [bit] NULL,
[ov] [money] NULL,
[DCer] [bit] NULL,
[mous] [int] NULL,
[iftatus] [int] NULL,
[PCos] [int] NULL,
[PDCus] [int] NULL,
[ersned] [int] NULL,
[ueKey] [uniqueidentifier] NULL,
[rified] [bit] NULL,
[Actr] [smalldatetime] NULL,
[embpe] [int] NULL,
[etAult] [money] NULL,
[t7] [money] NULL,
[t8] [money] NULL,
[6] [money] NULL,
[7] [money] NULL,
[8] [money] NULL,
[onalScore] [nvarchar](10) NULL,
[] [int] NULL,
[rganment] [int] NULL,
[rac] [int] NULL,
[kerpdate] [datetime] NULL,
[keriew] [datetime] NULL,
[ari] [int] NULL,
[] [int] NULL,
[Q1] [int] NULL,
[Q2] [int] NULL,
[Q3] [int] NULL,
[Q4] [int] NULL,
[Q5] [int] NULL,
[Q6] [int] NULL,
[Q7] [int] NULL,
[Q8] [int] NULL,
[Q9] [int] NULL,
[Q10] [int] NULL,
 CONSTRAINT [PK_tbl_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Результат

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

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

Я просто думаю, что мне нужно будет использовать промежуточную таблицу или другой программный подход с использованием промежуточного программного обеспечения или SQLBulkCopy (С#.NET), и я бы предпочел не делать этого на данном этапе. Я бы просто хотел, чтобы файл карты работал.

Пропустил ли я что-то, или это случай, когда вы стреляете в BULK INSERT с карточным файлом для большой лошади и катайтесь по-другому?

Ответ 1

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

Вы можете создать представление соответствующих столбцов на tbl_person и вставить в него.

В качестве альтернативы вы можете использовать файл формата не XML в старом стиле или (возможно, проще, если это позволяет его параметр безопасности среды) использовать OPENROWSET(BULK...) - обе эти опции описаны в учебнике.

Есть несколько других вещей, которые вы можете рассмотреть:

1 - Определение ширины таблицы образца не соответствует вашему входному файлу несколькими способами:

Нет источника в вашем файле для столбца NOT NULL DateCreated - вам может понадобиться ограничение DEFAULT для установки значения - возможно, это присутствует, но было исключено из определения таблицы примеров?
В таблице нет столбцов FK_Gender, JobTitle, Location, Department или EMailAddress1, даже если они указаны в файле формата ссылки - это может быть побочным эффектом вашей редакции столбца имена.

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

bcp YFP..tbl_Person_Importtest format nul -f c:\temp\so.bcp.gen.test.fmt -c -x -T -t ,

3 - Снимок экрана вашей команды BULK INSERT включает в себя команду:

SET IDENTITY INSERT <table> OFF

перед объемной вставкой. Есть две проблемы:

Это не делает ничего в контексте команды BULK INSERT, где вы бы использовали KEEPIDENTITY.
Настройка IDENTITY INSERT OFF отключает вставку идентификационных значений (т.е. нормальное поведение). Если вы используете метод OPENROWSET(BULK...), вам нужно установить IDENTITY INSERT ON перед запуском команды, чтобы включить установку идентификатора, а затем IDENTITY INSERT OFF после завершения команды.

Ответ 2

Число столбцов не является проблемой bcp для вашего случая.

Скорее всего, причиной является несоответствие Datatype или FK.

Для отладки.

Ограничения на падение в таблице

ИЛИ

Создайте копию таблицы (выберите * в соблазнительной таблице, где 1 = 2)

Предоставляйте BCP для соблазнительной опции -e, если в файле ошибок есть записи, то это проблема с типом данных/форматом.

Если данные копируются в temptable, тогда проверьте все ограничения, такие как fk, ak....