Как на T-SQL загрузить данные из Excel? Распределенные запросы

В Microsoft SQL Server существует возможность в SQL запросах, на языке T-SQL, запрашивать данные из внешних источников, сегодня мы рассмотрим несколько способов обращения к файлу Excel из запроса с целью получения данных.

В языке T-SQL это можно реализовать с помощью так называемых «Распределенных запросов», которые предполагают использование следующих конструкций:

OPENDATASOURCE
OPENROWSET
OPENQUERY

Сейчас мы рассмотрим все эти конструкции, и напишем несколько примеров.

Примечание! Загрузить данные из Excel можно и без использования языка T-SQL, в Microsoft SQL Server разработаны специальные службы SSIS (SQL Server Integration Services), в частности реализован инструмент по загрузке данных из разных источников «Импорт и экспорт данных», который позволяет загружать данные, уже не прибегая к использованию языка T-SQL.

Содержание

Исходные данные и требования
Функция OPENDATASOURCE в T-SQL
Функция OPENROWSET в T-SQL
Функция OPENQUERY в T-SQL

Исходные данные и требования

В качестве примера давайте представим, что нам нужно обратиться к файлу «TestExcel.xls», который расположен на диске D. Сами данные расположены на листе со стандартным названием «Лист1», они имеют следующую структуру.

ProductId	CategoryId	ProductName	Price
1	1	Клавиатура	100
2	1	Мышь	50
3	2	Телефон	300

Все примеры будут выполнены в Microsoft SQL Server 2016 Express.

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

Включается возможность использования распределённых запросов с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполните следующую SQL инструкцию.

   
   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1;
   RECONFIGURE;
   GO

Параметром show advanced options мы отображаем дополнительные параметры системной хранимой процедуры sp_configure, а командой RECONFIGURE мы применяем новые параметры.

Функция OPENDATASOURCE в T-SQL

OPENDATASOURCE – функция возвращает ссылку на источник данных, который может использоваться как часть четырехсоставного имени объекта.

Данная функция принимает два параметра, первый (provider_name) — это имя поставщика OLE DB, второй (init_string) – строка подключения.

Пример.

   
   SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                                                         'Data Source=D:\TestExcel.xls;
                                                         Extended Properties=Excel 8.0')...[Лист1$];

Как видите, в функцию мы передали имя поставщика и строку подключения, а потом обратились к источнику, используя четырехсоставное имя объекта, т.е. к таблице на листе 1.

Функция OPENROWSET в T-SQL

OPENROWSET – функция подключается к источнику данных и выполняет необходимый запрос.

У данной функции уже много параметров, с помощью которых мы указываем все необходимые сведения о подключении, которые требуются для доступа к источнику данных OLE DB.

Для того чтобы обратиться к Excel, можно написать следующий запрос.

   
   SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                                                 'Excel 8.0;
                                                 Database=D:\TestExcel.xls', 
                                                 [Лист1$]);

Вместо [Лист1$] можно в апострофах указать необходимый SQL запрос, например:

   
   SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                                                 'Excel 8.0;
                                                 Database=D:\TestExcel.xls', 
                                                 'SELECT ProductName, Price FROM [Лист1$]');

Функция OPENQUERY в T-SQL

OPENDATASOURCE и OPENROWSET удобно использовать, если Вам требуется разово получить данные из определённых внешних источников, в нашем случае из Excel, но если Вам требуется постоянно обращаться к таким источникам, то имеет смысл создать специальный объект в SQL Server – он называется «Связанный сервер» (Linked Server). Данный объект будет настроен на конкретный источник, и к этому объекту Вы сможете обращаться практически как к обычной базе данных, не указывая при этом строку подключения.

Связанный сервер настраивается один раз, иными словами, при его создании Вы указываете все необходимые настройки, а затем просто обращаетесь к нему по имени, которое Вы укажете.

Для обращения к связанным серверам в SQL сервере как раз и используется функция OPENQUERY.

OPENQUERY – функция обращается к связанному серверу и выполняет указанный запрос. На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.

Связанный сервер сначала нужно создать, это можно сделать с помощью системной процедуры sp_addlinkedserver, доступ к данному серверу настраивается процедурой sp_addlinkedsrvlogin. Связанные серверы также можно создавать и с помощью среды SQL Server Management Studio (Обозреватель объектов-> Объекты сервера-> Связанные серверы).

Пример.

   
   --Создание связанного сервера
   EXEC dbo.sp_addlinkedserver @server = 'TEST_EXCEL', 
                                        @srvproduct='OLE DB', 
                                        @provider='Microsoft.Jet.OLEDB.4.0', 
                                        @datasrc='D:\TestExcel.xls', 
                                        @provstr='Excel 8.0'
   --Настройки безопасности (авторизации)
   EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname= 'TEST_EXCEL',
                                        @useself= 'False',
                                        @locallogin=NULL,
                                        @rmtuser=NULL,
                                        @rmtpassword=NULL

Более подробно про связанные серверы можете почитать в материале «Связанные серверы в MS SQL Server – создание и примеры использования».

После того как связанный сервер создан, в моем примере я его назвал TEST_EXCEL, мы можем к нему обратиться с помощью функции OPENQUERY.

   
   SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]')

Также к связанному серверу можно обратиться и без использования функции OPENQUERY, например:

   
   SELECT * FROM TEST_EXCEL...[Лист1$]

Но рекомендовано к связанным серверам обращаться через функцию OPENQUERY.

Как я уже отмечал, функцию OPENQUERY можно использовать и для модификации данных на связанном сервере в инструкциях INSERT, UPDATE или DELETE, например, для обновления данных, в нашем случае инструкция будет выглядеть следующем образом.

   
   UPDATE OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]') 
   SET Price = 700
   WHERE ProductId = 3;

   SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]')

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

У меня на этом все, надеюсь, материал был Вам полезен, пока!