Как в 1С 7.7 создать временную таблицу SQL на основе Таблицы Значений

В процессе разработки на платформе 1С:Предприятие 7.7 часто возникает необходимость оптимизировать сложные отчеты или обработки. Одним из наиболее эффективных способов является использование прямых запросов к SQL-серверу через внешнюю компоненту 1CPP. Типичная задача — передать данные, уже отобранные в ТаблицуЗначений (ТЗ), непосредственно на сторону SQL для дальнейшего объединения (JOIN) с физическими таблицами базы данных. Разберем подробно, как это сделать правильно, избежав распространенных ошибок с типами данных и синтаксисом.

Зачем использовать временные таблицы в прямых запросах

Когда мы работаем со стандартными запросами 1С 7.7, платформа сама заботится о передаче параметров. Однако при использовании прямых запросов (Direct Queries) мы получаем гораздо большую гибкость. Создание временной таблицы на основе ТЗ позволяет:

1. Ускорить выполнение запросов: фильтрация данных происходит на стороне сервера.
2. Обойти ограничения 1С: например, когда нужно передать в условие ИН (Список) тысячи элементов, что может привести к переполнению текстового буфера запроса.
3. Сложные манипуляции: выполнять вложенные подзапросы и сложные соединения, которые недоступны в штатном языке запросов 7.7.

Метод 1: Использование функции ВыполнитьSQL_ИзТЗ

Это наиболее технологичный и быстрый способ, предоставляемый объектом ODBCRecordset. Он позволяет "закачать" всю таблицу значений в SQL-таблицу одной командой. Рассмотрим пошаговый алгоритм.

Шаг 1: Подготовка Таблицы Значений

Важно, чтобы структура ТЗ соответствовала будущей SQL-таблице. Проанализируем пример создания ТЗ с подразделениями и числами:

ТабЮЛ = СоздатьОбъект("ТаблицаЗначений");
ТабЮЛ.НоваяКолонка("UL", "Справочник.Подразделения"); // Важно: латиница!
ТабЮЛ.НоваяКолонка("Priority", "Число", 10, 0);

// Заполнение ТЗ данными
Пока ... Цикл
    ТабЮЛ.НоваяСтрока();
    ТабЮЛ.UL = ТекущийЭлемент;
    ТабЮЛ.Priority = Счетчик;
КонецЦикла;

Обратите внимание: Имена колонок крайне рекомендуется задавать на латинице. Внутренние механизмы 1CPP могут некорректно обрабатывать кириллические идентификаторы при автоматическом формировании параметров вставки, что часто приводит к ошибке Invalid object name или State 42S02.

Шаг 2: Создание и заполнение временной таблицы

Теперь рассмотрим код, который создает временную таблицу в tempdb и загружает в нее данные.

рс = СоздатьОбъект("ODBCRecordset");

// Проверяем наличие таблицы и удаляем её, если она осталась от прошлого сеанса
рс.ВыполнитьИнструкцию("IF OBJECT_ID('tempdb..#PrioritetUL') IS NOT NULL DROP TABLE #PrioritetUL");

// Создаем таблицу с явным указанием типов
// Для справочников 1С 7.7 используем CHAR(9)
рс.ВыполнитьИнструкцию("CREATE TABLE #PrioritetUL (UL CHAR(9), Priority INT)");

// Подготавливаем шаблон вставки
ТекстВставки = "INSERT INTO #PrioritetUL (UL, Priority) VALUES (?, ?)";
Если рс.Подготовить(ТекстВставки) = 0 Тогда
    Предупреждение(рс.ПолучитьОписаниеОшибки());
    Возврат;
КонецЕсли;

// Массовая вставка данных из ТЗ
рс.ВыполнитьSQL_ИзТЗ(ТабЮЛ);

// Проверяем результат
ТЗ_Результат = рс.ВыполнитьИнструкцию("SELECT * FROM #PrioritetUL");
ТЗ_Результат.ВыбратьСтроку();

Типы данных в SQL для объектов 1С 7.7

Проанализируем, какие типы данных следует указывать в команде CREATE TABLE, чтобы они соответствовали типам 1С:

• Справочники и Документы: В SQL-версии 1С 7.7 внутренний идентификатор (ID) имеет длину 9 символов. Поэтому используем CHAR(9).
• Число: Если это целое число, подойдет INT или SMALLINT. Если число с дробной частью — используйте NUMERIC(15,2) (в соответствии с точностью в 1С).
• Дата: В SQL используется DateTime. 1CPP корректно преобразует дату 1С в этот формат.
• Строка: Используйте VARCHAR или CHAR с соответствующей длиной.

Если вы работаете с неопределенным типом справочника (когда в колонке могут быть ссылки на разные виды объектов), длина идентификатора в SQL составляет 13 символов (4 символа — ID вида объекта и 9 — сам ID). В этом случае указывайте CHAR(13).

Метод 2: Ручное формирование запроса INSERT (строковый метод)

Если по какой-то причине метод ВыполнитьSQL_ИзТЗ не подходит, или данных совсем немного, можно сформировать текстовый запрос в цикле. Посмотрим на пример реализации с использованием объекта MetaDataWork для правильного преобразования ссылок в строковые ID базы данных.

метаДата = СоздатьОбъект("MetaDataWork");
рс = СоздатьОбъект("ODBCRecordset");

рс.ВыполнитьИнструкцию("CREATE TABLE #TempTable (ID char(9), Val int)");

ТекстЗапроса = "INSERT INTO #TempTable (ID, Val) VALUES ";
ТекстЗначений = "";

ТабЮЛ.ВыбратьСтроки();
Пока ТабЮЛ.ПолучитьСтроку() = 1 Цикл
    // Преобразуем ссылку 1С в 9-символьный ID для SQL
    SQL_ID = метаДата.ЗначениеВСтрокуБД(ТабЮл.Юл);
    
    ТекстЗначений = ТекстЗначений + (?(ТекстЗначений = "", "", ",")) + 
                    "('" + SQL_ID + "', " + ТабЮл.Приоритет + ")";
КонецЦикла;

рс.Выполнить(ТекстЗапроса + ТекстЗначений);

Важный нюанс: Этот метод гораздо медленнее при больших объемах данных (более 500-1000 строк), так как SQL-серверу приходится парсить огромную строку запроса. Кроме того, существует ограничение на максимальную длину текста SQL-запроса.

Особенности работы с временными таблицами (# и ##)

В SQL Server существует два типа временных таблиц:

1. Локальные (#Имя): Видны только в рамках того соединения, в котором они были созданы. Это рекомендуемый вариант. Поскольку 1С 7.7 через 1CPP обычно использует одно активное соединение на объект рс, таблица будет доступна, пока живет этот объект.
2. Глобальные (##Имя): Видны всем пользователям SQL-сервера. Выясним причину, почему их лучше не использовать: если два пользователя одновременно запустят отчет, они будут пытаться писать в одну и ту же таблицу ##PrioritetUL, что приведет к конфликтам и неверным данным.

Типичные ошибки и способы их решения

Проанализируем ситуацию, когда код кажется верным, но возникает ошибка Invalid object name '#Table'.

• Разрыв соединения: Если вы создали таблицу одним экземпляром ODBCRecordset, а пытаетесь прочитать другим, SQL Server может не найти локальную временную таблицу, так как она привязана к сессии. Старайтесь выполнять CREATE, INSERT и основной SELECT через одну переменную рс.
• Кириллица в названиях: Как уже упоминалось, ВыполнитьSQL_ИзТЗ часто сбоит на русских именах колонок. Переименуйте колонки в ТЗ перед выгрузкой.
• Лишние параметры в типах: Например, синтаксис SMALLINT(2) является ошибочным для MS SQL Server. Правильно писать просто SMALLINT.
• Использование зарезервированных слов: Избегайте имен колонок типа Order, Group, Value. Если они необходимы, заключайте их в квадратные скобки [Order] в тексте запроса.

Рассмотренные подходы позволяют эффективно передавать данные из объектной модели 1С 7.7 в реляционную модель SQL, что открывает широкие возможности для оптимизации производительности системы.