Как правильно выполнить массовую вставку записей в SQL Server из 1С?

При попытке оптимизировать процесс обмена данными между 1С и SQL Server мы часто сталкиваемся с необходимостью добавления множества записей за один раз. Однако попытка собрать длинную строку с конструкцией INSERT INTO ... VALUES (...), (...), ... нередко приводит к ошибке Incorrect syntax near ','. Давайте разберемся, почему это происходит и как правильно организовать массовую загрузку данных.

Выясняем причины ошибки синтаксиса

Когда мы отправляем запрос через ADODB.Connection и Execute, мы полностью зависим от того, как этот запрос интерпретирует драйвер и сам сервер. Основные причины ошибок при массовом добавлении:

1. Ограничения драйвера: Старые драйверы ODBC могут не поддерживать современный синтаксис множественного добавления (поддерживается в MS SQL начиная с версии 2008).
2. Форматирование данных: Использование запятых вместо точек в дробных числах или наличие пробелов (например, в строке 47 335) приводит к тому, что SQL сервер воспринимает запятую как разделитель значений, а не десятичный разделитель.
3. Типы данных: Неявное преобразование типов в SQL может давать сбои, если форматирование строки не соответствует ожиданиям движка базы данных.

Шаг 1: Подготовка данных и отказ от форматирования

Прежде всего, приведем данные к «чистому» виду. SQL сервер не понимает пробелы внутри чисел как разделители разрядов, а запятая в качестве десятичного разделителя в SQL-коде запрещена. Для работы с числами всегда используйте точку. Если вы передаете даты, приводите их к формату YYYYMMDD или используйте стандартный ISO-формат YYYY-MM-DDTHH:MM:SS.

Шаг 2: Использование прямого OLEDB провайдера

Вместо стандартного Microsoft OLE DB Provider for ODBC Drivers, который является «оберткой» и часто имеет ограничения, попробуйте перейти на прямой SQLOLEDB или MSOLEDBSQL. Это позволит избежать лишних слоев абстракции и повысить стабильность выполнения команд.

Шаг 3: Оптимальная стратегия — пакетная вставка или временные таблицы

Если массовый INSERT всё равно вызывает ошибку, не обязательно писать по одной строке в цикле — это крайне медленно. Рассмотрим более эффективный подход — использование временной таблицы:

Алгоритм работы:

1. Создаем временную таблицу в SQL Server через CREATE TABLE #TempTable (...).
2. Выполняем загрузку данных небольшими порциями (например, по 100-500 строк), что значительно быстрее, чем по 1 строке, и не перегружает лог транзакций.
3. После заполнения временной таблицы выполняем один запрос: INSERT INTO TargetTable SELECT * FROM #TempTable.
4. Удаляем временную таблицу.

Пример реализации через пакетный запрос:

// Пример формирования пакета
ТекстЗапроса = "INSERT INTO #TempTable (Nomer, Summa) VALUES ";
Для Каждого Строка Из ДанныеЦикла Цикл
    ТекстЗапроса = ТекстЗапроса + "(" + Строка.Номер + ", " + Формат(Строка.Сумма, "ЧДЦ=2; ЧРД=.; ЧГ=0") + "),";
КонецЦикла;
// Убираем последнюю запятую и отправляем на сервер

Шаг 4: Использование параметризованных запросов

Чтобы навсегда забыть о проблемах с форматированием (пробелы, запятые, даты), лучше всего использовать объект ADODB.Command. При использовании параметров драйвер сам заботится о правильной передаче типов данных, исключая ошибки синтаксиса и угрозу SQL-инъекций.

Рассмотрим структуру использования:

Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "INSERT INTO Block_oper (Nomer, Summa) VALUES (?, ?)";
// Добавляем параметры
Параметр1 = Команда.CreateParameter("Nomer", 200, 1, 50, ЗначениеНомера);
Команда.Parameters.Append(Параметр1);
Команда.Execute();

Подведем итоги

Для решения задачи массовой загрузки данных в SQL Server из 1С, мы рекомендуем следующий порядок действий:

• Проверьте версию вашего драйвера подключения; если есть возможность, перейдите на прямой OLEDB провайдер.
• Очистите данные от лишних символов: используйте только точки в качестве разделителей дробной части и строгий формат дат.
• Для максимальной скорости используйте временные таблицы, в которые данные загружаются частями, а затем переносятся в основную таблицу одним запросом.
• Если объем данных позволяет, переходите на параметризованные запросы — это самый надежный способ избежать синтаксических ошибок, связанных с локализацией и форматами чисел.

Работа с внешними базами данных через 1С — это задача, требующая аккуратности в типах данных. Проанализировав ваш код, можно заметить, что передача \"красивых\" отчетов 1С (с пробелами в суммах) напрямую в SQL невозможна. Приведите данные к машиночитаемому виду, и проблема исчезнет сама собой!