Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Передать набор значений из Excel в БД MS SQL / 12 сообщений из 12, страница 1 из 1
10.03.2020, 18:50
    #39936066
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Здравствуйте!

Ситуация:
1. В БД MS SQL есть (будет) хранимая процедура (MERGE). Которая принимает в качестве параметра некую таблицу. С ней сравнивается таблица из БД и видоизменяется.
2. Есть файл Excel с данными (пусть это будет таблица 24 строки на 10 столбцов)
3. Нужно из VBA вызвать хранимую процедуру, передав в качестве параметра данные из п.2 как таблицу, потому, что MERGE работает с таблицами, а не с массивами, скажем.

Вопрос - каким образом в VBA сформировать таблцу, которая будет передаваться на вход MERGE?

P.S.: Сделать логику на VBA "если данных в БД нет, то INSERT, а если есть, то UPDATE" построчно я могу, но коллеги просят сделать именно через вызов хранимки и делать это за один раз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.03.2020, 21:49
    #39936103
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Indian, может лучше на стороне Excel сформировать XML/JSON(в зависимости от версии сервера скуля) с данными и его передавать как параметр NVARCHAR(MAX). В хранимке из него сделать таблицу и скормить MERGE.
Напрямую, передать как табличный параметр может не получится, но утверждать не стану, т.к не пробовал
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.03.2020, 10:36
    #39936203
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
HandKot, спасибо за вариант. Оставлю на резерв на подумать.


Ребят, а recordset - это таблица? Можно ли его как-то сформировать из Excel?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.03.2020, 20:37
    #39936432
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
recordset можно сформировать из excel. Может кому пригодится:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Public Sub Proc1()
    Dim conn As New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\MyBook.xlsm;Extended Properties=Excel 12.0 XML"
    Dim rs As ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [SheetName$A1:J24]", conn
    rs.Close
End Sub



Остается вопрос - скормится ли он в MERGE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.03.2020, 08:37
    #39936509
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Indian
recordset можно сформировать из excel. Может кому пригодится:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Public Sub Proc1()
    Dim conn As New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\MyBook.xlsm;Extended Properties=Excel 12.0 XML"
    Dim rs As ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [SheetName$A1:J24]", conn
    rs.Close
End Sub



Остается вопрос - скормится ли он в MERGE

не получится у Вас это в VB.
Это стало возможным только в .NET
Для этого создаете в БД пользовательский тип данных типа таблица и его указываете параметром в хранимой процедуре

используя Ваш подход, то можно внутри хранимой процедуры использовать OPENROWSET (OPENDATASET) к листу Excel, но в данном случае, файл Excel должен быть доступен для скуля и он не должен быть открыт
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.03.2020, 09:00
    #39936520
Focha
Focha
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Indian
Здравствуйте!

Ситуация:
1. В БД MS SQL есть (будет) хранимая процедура (MERGE). Которая принимает в качестве параметра некую таблицу. С ней сравнивается таблица из БД и видоизменяется.
2. Есть файл Excel с данными (пусть это будет таблица 24 строки на 10 столбцов)
3. Нужно из VBA вызвать хранимую процедуру, передав в качестве параметра данные из п.2 как таблицу, потому, что MERGE работает с таблицами, а не с массивами, скажем.

Вопрос - каким образом в VBA сформировать таблцу, которая будет передаваться на вход MERGE?

P.S.: Сделать логику на VBA "если данных в БД нет, то INSERT, а если есть, то UPDATE" построчно я могу, но коллеги просят сделать именно через вызов хранимки и делать это за один раз.


Данные из Excel на сервер можно передать:
1) Сервер открывает файл bulk insert или OPENROWSET - это самый быстрый процесс (тип данных при открытие Excel определяется вроде по первым 5 строчкам)
2) Сформировать с помощью VBA XML/JSON и передать на сервер - способ зависит от кол-во данных, мало быстро, много долго
3) Я пользуюсь этим в своей работе: На стороне VBA создать Recodset и на сервере создать временную таблицу и подключиться к ней в VBA и перенести данные из RS к Excel в RS на сервер , способ удобен тем, что можно настроить на стороне VBA проверку данных, скорость почти как во 2 п.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.03.2020, 14:07
    #39937095
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Спасибо за советы!
Буду пробовать когда хранимую процедуру предоставят.
Пока склоняюсь к автор3) Я пользуюсь этим в своей работе: На стороне VBA создать Recodset и на сервере создать временную таблицу и подключиться к ней в VBA и перенести данные из RS к Excel в RS на сервер, способ удобен тем, что можно настроить на стороне VBA проверку данных, скорость почти как во 2 п описанному Focha. Тем более, что особой скорости для таблицы 24х10 не требуется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.03.2020, 14:09
    #39938260
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Focha, случайно не подскажете как вызвать хранимую процедуру с входными параметрами у которых тип данных определен пользователем? То есть, я создал таблицу tableName в БД, записал в нее данные. Теперь пытаюсь скормить ее в хранимку вот так:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Set cmdIns = New ADODB.Command
    With cmdIns
        .ActiveConnection = Conn
        .CommandText = "dbo.StoredProc_Name"
        .CommandType = adCmdStoredProc
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@Param_Name", adUserDefined, adParamInput, , tableName)
        .Execute
    End With



В самой хранимке:

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TYPE [Param_Name] AS TABLE(
    [Field1] VARCHAR(max) NOT NULL,
    [Field2] [DateTime] NOT NULL,
    [Field3] [float] NOT NULL,
    ...
)
GO



Что только я не писал вместо adUserDefined
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.03.2020, 16:29
    #39938346
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Вопрос снимается!

Indian
Focha, случайно не подскажете как вызвать хранимую процедуру с входными параметрами у которых тип данных определен пользователем? То есть, я создал таблицу tableName в БД, записал в нее данные. Теперь пытаюсь скормить ее в хранимку вот так:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Set cmdIns = New ADODB.Command
    With cmdIns
        .ActiveConnection = Conn
        .CommandText = "dbo.StoredProc_Name"
        .CommandType = adCmdStoredProc
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@Param_Name", adUserDefined, adParamInput, , tableName)
        .Execute
    End With



В самой хранимке:

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TYPE [Param_Name] AS TABLE(
    [Field1] VARCHAR(max) NOT NULL,
    [Field2] [DateTime] NOT NULL,
    [Field3] [float] NOT NULL,
    ...
)
GO



Что только я не писал вместо adUserDefined
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 08:30
    #39938512
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Indian
Вопрос снимается!



скажите как смоги, тоже интересно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.04.2020, 21:04
    #39949657
Indian
Indian
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
HandKot, прошу прощения за задержку. В итоге было реализовано так:

1. Хранимая процедура была переписано таким образом, чтобы она вызывалась с передачей ей имени временной таблицы.
2. Макрос VBA записывает данные из файла Excel во временную таблицу и вызывает хранимую процедуру передавая ей имя таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2020, 08:00
    #39949852
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Передать набор значений из Excel в БД MS SQL
Indian, неплохое решение
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Передать набор значений из Excel в БД MS SQL / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 7064ms
Закрыть
start [/forum/topic.php?fid=60&tablet=1&tid=2154815]:
 0ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 6962ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 10ms
total:  7064ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]