Выгрузка данных в таблицу MS SQL SERVER

02.07.2019 17 Comments

Внешние источники данных, Разработка, Системная интеграция

    Публикация.jpg
Процедура по выгрузке данных из 1C (таблица значений) в таблицу MS SQL SERVER через COM.

Процедура предназначена для выгрузки данных из таблицы значений в таблицу на MS SQL SERVER. Имена колонок таблицы 1с и таблицы на SQL сервера должны совпадать.

Процедура НазваниеПроцедуры….(ТаблицаДанных)
    

Сервер =  ..... Наименование сервера ms sql;
НазваниеБазы = Имя базы данных;
НазваниеСхемы = ....
НазваниеТаблицы = ....
Пользователь = .....
Пароль = .....
Попытка

Подключение к ms sql server

    SQL_Соединение = Новый COMОбъект("ADODB.Connection");

SQL_Соединение.Open("Provider=sqloledb; Server=" + Сервер + " ;Database=" + НазваниеБазы + "; User Id=" + Пользователь + ";Password=" + Пароль + ";");
ПодключениеSQL = Истина;

Исключение

Сообщить("Соединение Не Установлено");
ПодключениеSQL = Ложь;
Возврат;
КонецПопытки;

Если ПодключениеSQL Тогда

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = SQL_Соединение;

 

Очистка таблицы для загрузки данных, можно не выполнять, если надо догрузить данные в таблицу
        

 

    Command.CommandText = "DELETE FROM [" + НазваниеБазы + "].[" + НазваниеСхемы + "].[" + НазваниеТаблицы + "]";
Command.Execute();

Выборка данных о типах колонок в таблице на ms sql server
        

       Command.CommandText = "SELECT TABLE_CATALOG
|,TABLE_SCHEMA
|,TABLE_NAME
|,COLUMN_NAME
|,IS_NULLABLE
|,DATA_TYPE
|,CHARACTER_MAXIMUM_LENGTH
|,NUMERIC_PRECISION
|FROM INFORMATION_SCHEMA.COLUMNS
|Where INFORMATION_SCHEMA.COLUMNS.table_catalog = '" + НазваниеБазы + "'
|AND INFORMATION_SCHEMA.COLUMNS.table_schema = '" + НазваниеСхемы + "'
|AND INFORMATION_SCHEMA.COLUMNS.table_name = '" + НазваниеТаблицы + "'
|ORDER BY TABLE_SCHEMA, TABLE_NAME";


Выборка=Неопределено;
Попытка
Выборка = Command.Execute();
Исключение

Сообщить("Не сделана выборка колонок из таблицы " + НазваниеТаблицы + " на MS SQL сервере");

Если SQL_Соединение <> Неопределено Тогда
SQL_Соединение.Close();
КонецЕсли;

Выборка                     = Неопределено;
SQL_Соединение                 = Неопределено;

Возврат;

КонецПопытки;


ИменаКолонокМассив = Новый Массив;
СтруктураИменКолонокИДлин = Новый Структура;


ТекстЗапросаSQL= "INSERT INTO [" + НазваниеБазы + "].[" + НазваниеСхемы + "].[" + НазваниеТаблицы + "]
| (";

ПерваяКолонка = Истина;

Пока Выборка.EOF() = 0 Цикл

//Добавляем
ИмяКолонки      = Выборка.Fields("COLUMN_NAME").Value;


ИменаКолонокМассив.Добавить(ИмяКолонки);

Если ПерваяКолонка Тогда
ТекстЗапросаSQL = ТекстЗапросаSQL + "[" + ИмяКолонки + "]";
Иначе
ТекстЗапросаSQL = ТекстЗапросаSQL + "," + "[" + ИмяКолонки + "]";
КонецЕсли;
ПерваяКолонка = Ложь;
Выборка.MoveNext();
КонецЦикла;

ТекстЗапросаSQL = ТекстЗапросаSQL + ")
|  VALUES (";

ТекстЗапросаДоVALUES = ТекстЗапросаSQL;

КолВсего = ТаблицаДляЗагрузки.Количество();
КолТекущая = 0;

//Выгрузка данных из таблицы значений
Для Каждого СтрТаб Из ТаблицаДанных Цикл
ПерваяКолонка = Истина;
ТекстЗапросаSQL = ТекстЗапросаДоVALUES;


Для Каждого КолИмя Из  ИменаКолонокМассив Цикл

Если ПерваяКолонка Тогда

ТекстЗапросаSQL = ТекстЗапросаSQL + СтрТаб[КолИмя];

Иначе

ТекстЗапросаSQL = ТекстЗапросаSQL + "," + СтрТаб[КолИмя];
КонецЕсли;

ПерваяКолонка = Ложь;
КонецЦикла;

ТекстЗапросаSQL = ТекстЗапросаSQL +")";
Command.CommandText = ТекстЗапросаSQL;
Command.Execute();

КолТекущая = КолТекущая + 1;
КонецЦикла;



Если SQL_Соединение <> Неопределено Тогда
SQL_Соединение.Close();
КонецЕсли;

Выборка                     = Неопределено;
SQL_Соединение                 = Неопределено;

Сообщить("Всего:" + КолВсего + "; Загружено:" + КолТекущая);

КонецЕсли;

Конец процедуры    

Related Posts

17 Comments

  1. bintape

    1) лучше подготовить один большой запрос и отдать серверу СУБД на исполнение, чем дергать каждый раз

    2) как вариант подготовить шаблон типа @q=’ INSERT INTO #bd# ….VALUES ##’ , а в 1С подставлять значения через СтрЗаменить() — намного улучшится читабельность кода для последующей отладки

    Reply ↓
  2. gosizo

    Спасибо, познавательно

    Reply ↓
  3. A_Max

    (1) А ещё лучше использовать для этого СтрСоединить и СтрШаблон.

    Шаблон = » INSERT INTO bd.tbl (value) VALUES (%1)»;
Результат = Новый Массив;
Для Каждого СтрокаТЧ Из ТЧ Цикл
Результат.Добавить( СтрШаблон(Шаблон, СтрокаТЧ.Значение));
КонецЦикла;

Результат = СтрСоединить(Результат, Символы.ПС);
    Reply ↓
  4. EvgenSav

    (3)Спасибо за дополнение, только есть один нюанс, что функция СтрШаблон появилась только в версии платформы с 8.3. а данный функционал работает и на платформе 8.2.

    (1)Спасибо, все верно лучше один запрос выполнить на сервере СУБД. В данном варианте, проще найти ошибку с данными, будет понятно на какой строке таблицы значений вылезла ошибка.

    Reply ↓
  5. user813409

    Если требуется полностью переписать таблицу существенно быстрее проходит операция очистки таблицы:

     Command.CommandText = «TRUNCATE TABLE [» + НазваниеБазы + «].[» + НазваниеСхемы + «].[» + НазваниеТаблицы + «]»;
    Reply ↓
  6. EvgenSav

    (5)согласен, спасибо.

    Reply ↓
  7. s0nya

    Сейчас думаю как в SQL через 1с загрузить файл на 1000000 строк. Почему через 1С? Это будут делать пользователи и учить их SSIS или чему другому желания нет. Операция обновления данных с перезаливкой раз в месяц. Данные нужны самой 1С.

    Не думали что можно данные выгрузить в файл и дать команду SQL импорта данных с файла? Должно быть намного быстрее.

    Reply ↓
  8. nomad_irk

    (7)Использовать ADO и параметризованый запрос, не?

    Reply ↓
  9. nomad_irk

    (0) Вы про параметры запроса при использовании ADO что-нибудь слышали?

    Reply ↓
  10. EvgenSav

    (9) да, слышал. я же не утверждаю что это единственный метод.

    Reply ↓
  11. nomad_irk

    (10) Так в случае передачи больших объемов данных формировать текст запроса, используя конкатенацию строк как минимум — глупо.

    Reply ↓
  12. EvgenSav

    (11) для больших объемов возможно да, про это уже писали в комментариях выше

    Reply ↓
  13. s0nya

    (8)

    ADO и параметризованый запрос

    Есть файл с 3-мя колонками (Артикул, наименование, вес) в формате csv. 1М строк. Понятно, что загружать в 1С этот файл, что то делать с данными и потом вставлять в параметр SQL желания нет. Или Вы не это имели в виду?

    Reply ↓
  14. nomad_irk

    (13)И вы решили запихать в MS SQL напрямую запрос, состоящий из 1М строк вида INS ERT IN TO <TableName>, так?

    В 1С достаточно выполнить «трансляцию» значений колонок из файла в значения параметров запроса, тем более вы все равно будете генерировать текст запроса SQL средствами 1С, если я правильно понял.

    Reply ↓
  15. s0nya

    (14)

    Нравится этот вариант. Файл на 250 000 залетает менее чем за секунду. Можете набросать вариант с параметрами. Явной трансляции для универсальности мне не хватает.

    BULK

    INSERT PDTest

    FROM ‘c: empUA_test.txt’

    WITH

    (

    CODEPAGE = ‘65001’,

    firstrow = 2,

    FIELDTERMINATOR = ‘;’,

    ROWTERMINATOR = ‘
    ‘

    )

    GO

    Reply ↓
  16. nomad_irk

    (15)Если такой вариант работает, то и 1М строк залетит за малое время. Для чего в таком случае привлекать 1С — вообще не понятно.

    В публикации используется конкатенация строк вида INS ERT IN TO <TableName> по количествую вставляемых в таблицу строк — в этом случае я предлагаю использовать параметризованный ADO запрос.

    Reply ↓
  17. s0nya

    (16)

    1С нужна как «Окно» / UI загрузки данных для людей привыкших к 1С. Данные и загружаются для использования в 1С, просто обновлять в системе миллионы строк не вижу оптимальным, там еще поиск нужно организовывать. В SQL это в десятки раз быстрее отрабатывает.

    Надеялся, что есть готовые наработки по этому направлению.

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *