Сегодня в материале мы с Вами рассмотрим несколько способов реализации того, как можно в Microsoft SQL Server сохранить результат выполнения хранимой процедуры в таблице в тех случаях, когда процедура возвращает табличные данные.
Это Вам может потребоваться, например, тогда когда нет возможности изменить код процедуры таким образом, чтобы непосредственно в самой процедуре осуществлялась вставка (INSERT) данных, которые она возвращает, в нужную таблицу.
Если у Вас встала подобная задача сразу скажу, что универсального способа я не нашел, каждый из перечисленных способов ниже имеет свои недостатки, иными словами, какой использовать решать Вам.
Для начала давайте создадим тестовую хранимую процедуру, которая будет возвращать табличные данные. Все действия ниже я буду выполнять на Microsoft SQL Server 2016 Express, на текущий момент вышла уже 2017 версия SQL Server, о том, что нового в ней появилось, можете почитать в материале – «Обзор основных нововведений в Microsoft SQL Server 2017».
Создание хранимой процедуры, которая возвращает табличные данные
Для примера давайте напишем простую процедуру, которая будет возвращать небольшую таблицу, состоящую всего из трех столбцов, данную таблицу я сформирую с помощью конструктора табличных значений. Для создания процедуры запускаем следующую инструкцию.
CREATE PROCEDURE sp_Test AS SELECT * FROM (VALUES (1, 'Компьютер', 500), (2, 'Принтер', 300), (3, 'Монитор', 300) ) AS TmpTable (ProductId, ProductName, Summa) GO EXEC sp_Test
Как видите, процедура создана и возвращает табличные данные.
Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.
Способ 1 – Используем вызов процедуры в конструкции INSERT INTO
Инструкция INSERT позволяет в качестве источника указывать вызов хранимой процедуры, но у данного способа есть один очень существенный недостаток, таблица, в которую Вы хотите сохранить данные, должна уже существовать, т.е. Вы заранее должны знать количество и тип данных возвращающихся столбцов, для того чтобы создать соответствующую таблицу.
В следующем примере мы создадим временную таблицу, выполним инструкцию INSERT, в которой в качестве источника будет выступать вызов хранимой процедуры.
--Создаем временную таблицу CREATE TABLE #TmpTable (ProductId INT, ProductName VARCHAR (30), Summa MONEY); --Осуществляем вставку INSERT INTO #TmpTable EXEC sp_Test --Проверяем, т.е. делаем выборку из таблицы SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable
Способ 2 – Используем связанный сервер
В данном случае мы создаем связанный сервер, который будет ссылаться на самого себя, иными словами, на текущий сервер. Затем с помощью конструкции OPENQUERY мы обращаемся к связанному серверу, запуская на нем соответствующую процедуру. Результат в данном случае мы можем сохранять уже с помощью конструкции SELECT INTO в новую таблицу (в нашем случае для примера во временную таблицу).
--Создаем связанный сервер EXEC sp_addlinkedserver @server = N'CurrentServer', @srvproduct = N'', @provider = N'SQLOLEDB', @datasrc = N'TESTSERVER\SQLEXPRESS' GO --Выполняем запрос с сохранением данных во временную таблицу SELECT * INTO #TmpTable FROM OPENQUERY(CurrentServer, 'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test') --Проверяем полученные данные SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable
Плюс данного способа в том, что Вам уже не нужно заранее создавать таблицу и соответственно знать количество столбцов. Но, как Вы понимаете, у данного способа есть и недостатки, например, Вы должны для выполнения таких процедур предварительно создать связанный сервер, также данный способ не будет работать, если табличные данные не имеют названия колонок (например, SELECT 1, 2, 3) и если в хранимой процедуре используются временные таблицы.
Способ 3 – Используем конструкцию OPENROWSET
Этот способ подразумевает использование функции OPENROWSET и поставщика OLE DB. Для использования данного способа у Вас должен быть включен параметр Ad Hoc Distributed Queries.
--Включаем параметр Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE GO --Выполняем запрос с сохранением данных во временную таблицу SELECT * INTO #TmpTable FROM OPENROWSET('SQLOLEDB', 'Server=TESTSERVER\SQLEXPRESS;Trusted_Connection=Yes;', 'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test') --Проверяем полученные данные SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable
Плюс этого способа в том, что Вам уже не нужно предварительно создавать ни таблицу, ни связанный сервер. Но, минусы все равное есть, конструкцию использовать не получится, если в хранимой процедуре используются временные таблицы или есть неименованные столбцы, и, как я уже сказал, предварительно нужно включить параметр «Ad Hoc Distributed Queries».
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
У меня все, надеюсь, материал был Вам полезен, пока!