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

← На главную

Разработчики 1С часто сталкиваются с необходимостью интеграции с внешними базами данных. Для этого нередко используются прямые запросы через COM-объект ADODB.Connection. Однако при написании сырых SQL-запросов легко совершить синтаксическую ошибку, особенно если логика работы СУБД путается с объектной логикой платформы 1С. Рассмотрим типичную проблему: попытку вставить или обновить данные в СУБД с использованием условия WHERE, разберем причины ошибок и изучим правильные подходы к решению этой задачи.

Выясняем причину ошибки: разница между INSERT и UPDATE

В исходном вопросе автор темы пытается выполнить следующий запрос:

INSERT INTO [dbo].[PLUSet] ([setNo]) WHERE Filter=0 VALUES (Значение_setNo)

Данный запрос возвращает ошибку синтаксиса СУБД. Проанализируем, почему так происходит. В классическом языке SQL операторы манипулирования данными (DML) имеют строго разграниченные роли:

Оператор INSERT предназначен исключительно для создания новых записей в таблице. Поскольку строка еще не существует в базе данных, к ней физически невозможно применить фильтр WHERE. СУБД не знает, какую существующую строку анализировать на соответствие условию Filter=0, так как ее цель — вставить абсолютно новую порцию данных.
Оператор UPDATE используется для модификации уже существующих записей в таблице. Именно этот оператор поддерживает конструкцию WHERE, которая указывает СУБД, какие именно строки необходимо обновить.

Таким образом, если задача состоит в том, чтобы изменить значение колонки setNo в тех строках таблицы, где колонка Filter равна нулю, классический синтаксис SQL требует применения оператора UPDATE.

Правильный SQL-запрос для изменения существующих строк будет выглядеть так:


UPDATE [dbo].[PLUSet]
SET [setNo] = @Значение_setNo
WHERE [Filter] = 0;

При реализации этого запроса в коде 1С через ADO, формирование строки запроса будет выглядеть следующим образом:


ТекстЗапросаADO = "UPDATE [dbo].[PLUSet] 
                  |SET [setNo] = " + Формат(Значение_setNo, "ЧГ=0") + " 
                  |WHERE [Filter] = 0";

Когда условие WHERE всё-таки можно использовать при вставке (INSERT)

Существуют специфические сценарии, когда вставка новых записей должна зависеть от определенного условия. В таких случаях SQL позволяет комбинировать оператор INSERT с выборкой SELECT, в которой уже допустимо использование конструкции WHERE.

Сценарий 1. Вставка данных из другой таблицы с фильтрацией.
Если нам нужно перенести данные из одной таблицы в другую, но только по определенным строкам:


INSERT INTO [dbo].[PLUSet] ([setNo])
SELECT [someField] 
FROM [anotherTable]
WHERE [Filter] = 0;

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


INSERT INTO [dbo].[PLUSet] ([setNo])
SELECT @Значение_setNo
WHERE NOT EXISTS (
    SELECT 1 
    FROM [dbo].[PLUSet] 
    WHERE [Filter] = 0
);

Продвинутый сценарий: Использование оператора MERGE (UPSERT)

На практике часто возникает комплексная задача: обновить существующую запись, если она удовлетворяет условию, или создать новую, если такой записи еще нет. В современных СУБД (например, MS SQL Server) для этой цели предназначен мощный оператор MERGE. Он позволяет выполнить обе операции за один транзакционный запрос, снижая нагрузку на сеть и базу данных.

Посмотрим на пример реализации логики «вставить или обновить» с условием:


MERGE [dbo].[PLUSet] AS target
USING (SELECT @Значение_setNo AS setNo, 0 AS Filter) AS source
ON (target.Filter = source.Filter)
WHEN MATCHED THEN
    UPDATE SET target.setNo = source.setNo
WHEN NOT MATCHED THEN
    INSERT (setNo, Filter) VALUES (source.setNo, source.Filter);

Современная альтернатива: Внешние источники данных в 1С

Начиная с версии платформы 8.3.5, разработчикам больше нет необходимости вручную писать сложные SQL-запросы и контролировать правильность их синтаксиса через COM-соединения. Платформа 1С предоставляет встроенный объектный механизм — «Внешние источники данных» (ВИД).

Подключив таблицу сторонней СУБД в качестве внешнего источника данных в конфигураторе, мы получаем возможность работать с ней как с привычной таблицей 1С. Платформа сама транслирует объектные методы в корректный SQL-синтаксис конкретной СУБД (будь то MS SQL Server, PostgreSQL, MySQL или Oracle).

Разберем по шагам, как решить задачу автора темы с помощью встроенного языка 1С без написания сырого SQL:


// Получаем менеджер таблицы внешнего источника данных
ВнешняяТаблица = ВнешниеИсточникиДанных.ИмяИсточника.Таблицы.PLUSet;

// Настраиваем отбор по условию Filter = 0
ПараметрыОтбора = Новый Структура("Filter", 0);
Выборка = ВнешняяТаблица.Выбрать(ПараметрыОтбора);

// Обходим найденные записи в цикле и изменяем их
Пока Выборка.Следующий() Цикл
    ОбъектЗаписи = Выборка.ПолучитьОбъект();
    ОбъектЗаписи.setNo = Значение_setNo;
    
    // Платформа автоматически сгенерирует безопасный SQL-запрос UPDATE с условием WHERE
    ОбъектЗаписи.Записать(); 
КонецЦикла;

Преимущества использования Внешних источников данных:

Безопасность кода и защита от SQL-инъекций.
Строгая типизация данных платформы 1С.
Отсутствие зависимости от синтаксиса конкретной СУБД (код останется рабочим при миграции, например, с MS SQL на PostgreSQL).
Возможность использования стандартных механизмов транзакций 1С.

Нужно ли обновлять таблицу в SQL и в 1С после записи?

В сообщении 0 автор интересуется: «И нужно ли обновлять таблицу PLUSet (в таблицу куда пишу) после записи?». Разделим этот вопрос на два уровня: со стороны СУБД и со стороны интерфейса 1С.

1. На уровне СУБД (SQL Server)
Никаких принудительных команд обновления таблиц выполнять не требуется. После успешного завершения транзакции (выполнения запроса UPDATE или метода Записать()) база данных мгновенно фиксирует изменения в файле данных и транзакционном логе. Новые данные сразу же становятся доступны для любых других параллельных и последующих запросов.

2. На уровне клиентского интерфейса 1С
Если данные из внешней таблицы выводятся пользователю на форму 1С (например, в динамический список или в таблицу значений), то они кэшируются платформой. Если запись была произведена напрямую через SQL-запрос (в обход объектной техники 1С), сервер приложений 1С никак не узнает об изменении данных в сторонней СУБД.

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


// Метод обновляет данные динамического списка, считывая их заново из базы данных
Элементы.СписокPLUSet.Обновить();

Критически важное предупреждение о безопасности

Если таблица PLUSet, в которую производится прямая запись, является внутренней («родной») таблицей самой информационной базы 1С (например, таблицей справочника, документа или регистра сведений), то любые прямые операции записи (INSERT, UPDATE, DELETE) через СУБД категорически запрещены!

Нарушение этого правила влечет за собой серьезные последствия:

Нарушение лицензионного соглашения: Прямая модификация структуры и данных БД 1С средствами сторонних утилит или прямых запросов нарушает условия лицензии «1С:Предприятие».
Рассинхронизация кэша сервера приложений: Кластер серверов 1С активно кэширует считанные данные в оперативной памяти. Изменение данных напрямую в СУБД приведет к тому, что платформа продолжит выдавать пользователям устаревшие данные из своего кэша, что спровоцирует непредсказуемые сбои.
Разрушение целостности базы данных: При прямой записи средствами SQL не отрабатывает бизнес-логика конфигурации (события ПередЗаписью, ПриЗаписи), не контролируется ссылочная целостность и не обновляются сопутствующие индексы и таблицы итогов, что гарантированно приведет к логическому повреждению базы данных.

Для работы с собственными таблицами 1С всегда используйте исключительно штатные объектные методы платформы (СоздатьЭлемент(), СоздатьМенеджерЗаписи(), Записать()).