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

Question

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

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

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

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

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

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

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

Вот это в простом тексте:

1,fArty,Padul,1,10/1/1962,Head of ,Australia,AU Talavera Centre,NSW,7 CSU,farty.randy@gummibaer.com
2,mifsm,Jodel,1,10/1/1970,Chief Officer,Australia,AU ,NSW,8 CSU,midsm@gummibaer.com

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

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

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

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

    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), поскольку таблица базы данных пуста, а файл импорта не имеет индекса. Это нормально работает для небольшой таблицы назначения, поскольку база данных генерирует индекс первичного ключа.

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

<?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 формат nul -f PersonImportMapFile.xml -c -x -T

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

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

БОЛЬШАЯ ТАБЛИЦА Я не могу включить полное описание из-за проблем с интеллектуальной собственностью, но большая таблица назначения имеет 146 полей без разреженных полей и множество полей DATETIME и DATE, а также стеки внешних ключей (в основном INT), некоторые из которых являются обнуляемый. Вот файл карты, сгенерированный 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 (C# .NET), и я бы предпочел не делать этого на этом этапе. Я просто хотел бы, чтобы файл карты работал.

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

12

sql sql-server database bulkinsert map-files

Источник

user3098527 24 мар '17 в 05:53

2 ответа

Решение

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

Наиболее вероятная причина - несоответствие типа данных или проблема FK.

Для отладки.

Отбросьте ограничения на столе

ИЛИ ЖЕ

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

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

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

1

Источник

user6085803 24 мар '17 в 06:26

Другие вопросы по тегам sql sql-server database bulkinsert map-files

user27825 27 мар '17 в 08:40 2017-03-27 08:40 · Accepted Answer · 2017-03-27 08:40

Возможно, вы упустили то, что пример в руководстве, который использует файл формата 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 после завершения команды.