Вставьте результаты хранимой процедуры во временную таблицу
Как мне сделать SELECT * INTO [temp table] FROM [stored procedure]? Не FROM [Table] и без определения [temp table]?
Select все данные из BusinessLine в tmpBusLine работает отлично.
select *
into tmpBusLine
from BusinessLine
Я пытаюсь то же самое, но с помощью stored procedure который возвращает данные, не совсем то же самое.
select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'
Выходное сообщение:
Сообщение 156, уровень 15, состояние 1, строка 2 Неверный синтаксис рядом с ключевым словом "exec".
Я прочитал несколько примеров создания временной таблицы с той же структурой, что и для хранимой процедуры вывода, которая прекрасно работает, но было бы неплохо не указывать столбцы.
34 ответа
Вы можете использовать OPENROWSET для этого. Посмотри. Я также включил код sp_configure, чтобы включить специальные распределенные запросы, если он еще не включен.
CREATE PROC getBusinessLineHistory
AS
BEGIN
SELECT * FROM sys.databases
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
'EXEC getBusinessLineHistory')
SELECT * FROM #MyTempTable
Если вы хотите сделать это без предварительного объявления временной таблицы, вы можете попытаться создать пользовательскую функцию, а не хранимую процедуру, и заставить эту пользовательскую функцию возвращать таблицу. В качестве альтернативы, если вы хотите использовать хранимую процедуру, попробуйте что-то вроде этого:
CREATE TABLE #tmpBus
(
COL1 INT,
COL2 INT
)
INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
В SQL Server 2005 вы можете использовать INSERT INTO ... EXEC вставить результат хранимой процедуры в таблицу. Из MSDN INSERT документация (фактически для SQL Server 2000):
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
Это ответ на слегка измененную версию вашего вопроса. Если вы можете отказаться от использования хранимой процедуры для пользовательской функции, вы можете использовать встроенную табличную пользовательскую функцию. По сути, это хранимая процедура (будет принимать параметры), которая возвращает таблицу как набор результатов; и, следовательно, будет хорошо с заявлением INTO.
Вот хорошая быстрая статья об этом и других пользовательских функциях. Если у вас все еще есть потребность в хранимой процедуре, вы можете заключить встроенную табличную пользовательскую функцию в хранимую процедуру. Хранимая процедура просто передает параметры, когда она вызывает select * из встроенной пользовательской функции с табличным значением.
Так, например, у вас будет встроенная пользовательская функция с табличным значением для получения списка клиентов для определенного региона:
CREATE FUNCTION CustomersByRegion
(
@RegionID int
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM customers
WHERE RegionID = @RegionID
GO
Затем вы можете вызвать эту функцию, чтобы получить такие результаты:
SELECT * FROM CustomersbyRegion(1)
Или сделать ВЫБОР В:
SELECT * INTO CustList FROM CustomersbyRegion(1)
Если вам все еще нужна хранимая процедура, оберните эту функцию следующим образом:
CREATE PROCEDURE uspCustomersByRegion
(
@regionID int
)
AS
BEGIN
SELECT * FROM CustomersbyRegion(@regionID);
END
GO
Я думаю, что это самый "безрассудный" метод для получения желаемых результатов. Он использует существующие функции так, как они были предназначены для использования без дополнительных сложностей. Вложив встроенную пользовательскую функцию с табличным значением в хранимую процедуру, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для реального кода SQL.
Было предложено использовать OPENROWSET, но это не то, для чего предназначалась функция OPENROWSET (из Books Online):
Содержит всю информацию о соединении, необходимую для доступа к удаленным данным из источника данных OLE DB. Этот метод является альтернативой доступу к таблицам на связанном сервере и является одноразовым, специальным методом подключения и доступа к удаленным данным с использованием OLE DB. Для более частых ссылок на источники данных OLE DB используйте вместо этого связанные серверы.
Использование OPENROWSET выполнит работу, но потребует дополнительных затрат на открытие локальных соединений и сортировку данных. Это также может быть не вариант во всех случаях, так как требует специального разрешения запроса, которое представляет угрозу безопасности и, следовательно, может быть нежелательным. Кроме того, подход OPENROWSET исключает использование хранимых процедур, возвращающих более одного набора результатов. Этого можно достичь, заключив в одну хранимую процедуру несколько встроенных пользовательских функций табличного значения.
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE
SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
Самое простое решение:
CREATE TABLE #temp (...); INSERT INTO #temp EXEC [sproc];
Если вы не знаете схему, вы можете сделать следующее. Обратите внимание, что в этом методе существуют серьезные угрозы безопасности.
SELECT *
INTO #temp
FROM OPENROWSET('SQLNCLI',
'Server=localhost;Trusted_Connection=yes;',
'EXEC [db].[schema].[sproc]')
Когда хранимая процедура возвращает много столбцов, и вы не хотите вручную "создавать" временную таблицу для хранения результата, я обнаружил, что самый простой способ - перейти в хранимую процедуру и добавить предложение "в" в последний оператор select и добавьте 1=0 к предложению where.
Запустите хранимую процедуру один раз, вернитесь и удалите только что добавленный код SQL. Теперь у вас будет пустая таблица, соответствующая результату хранимой процедуры. Вы можете либо "скрипт таблицы как создать" для временной таблицы, либо просто вставить непосредственно в эту таблицу.
declare @temp table
(
name varchar(255),
field varchar(255),
filename varchar(255),
filegroup varchar(255),
size varchar(255),
maxsize varchar(255),
growth varchar(255),
usage varchar(255)
);
INSERT @temp Exec sp_helpfile;
select * from @temp;
Если таблица результатов вашего хранимого процесса слишком сложна, чтобы вручную ввести оператор "создать таблицу", и вы не можете использовать OPENQUERY OR OPENROWSET, вы можете использовать sp_help для генерации списка столбцов и типов данных. Если у вас есть список столбцов, вам нужно просто отформатировать его в соответствии с вашими потребностями.
Шаг 1: Добавьте "в #temp" к выходному запросу (например, "выберите [...] в #temp from [...]").
Самый простой способ - это напрямую редактировать выходной запрос в proc. Если вы не можете изменить сохраненный процесс, вы можете скопировать содержимое в новое окно запроса и изменить там запрос.
Шаг 2: Запустите sp_help для временной таблицы. (например, "exec tempdb..sp_help #temp")
После создания временной таблицы выполните sp_help для временной таблицы, чтобы получить список столбцов и типов данных, включая размер полей varchar.
Шаг 3: Скопируйте столбцы и типы данных в оператор создания таблицы
У меня есть лист Excel, который я использую для форматирования вывода sp_help в оператор "создать таблицу". Вам не нужно ничего такого, что нужно, просто скопируйте и вставьте в свой редактор SQL. Используйте имена столбцов, размеры и типы, чтобы создать оператор "Создать таблицу #x [...]" или "объявить таблицу @ [[]]", который можно использовать, чтобы ВСТАВИТЬ результаты хранимой процедуры.
Шаг 4: Вставить во вновь созданную таблицу
Теперь у вас будет запрос, аналогичный другим решениям, описанным в этой теме.
DECLARE @t TABLE
(
--these columns were copied from sp_help
COL1 INT,
COL2 INT
)
INSERT INTO @t
Exec spMyProc
Этот метод также может быть использован для преобразования временной таблицы (#temp) к табличной переменной (@temp). Хотя это может быть больше шагов, чем просто написание create table Заявите сами, это предотвращает ручные ошибки, такие как опечатки и несоответствия типов данных в больших процессах. Отладка опечатки может занять больше времени, чем написание запроса.
Ваша хранимая процедура только получает данные или изменяет их тоже? Если он используется только для извлечения, вы можете преобразовать хранимую процедуру в функцию и использовать выражения общих таблиц (CTE), не объявляя ее следующим образом:
with temp as (
select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp
Однако все, что необходимо извлечь из CTE, следует использовать только в одном операторе. Вы не можете сделать with temp as ... и попробуйте использовать его после нескольких строк SQL. Вы можете иметь несколько CTE в одном операторе для более сложных запросов.
Например,
with temp1020 as (
select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020
where id not in (select id from temp2030)
Если OPENROWSET вызывает у вас проблемы, с 2012 года существует другой путь; использовать sys.dm_exec_describe_first_result_set_for_object, как упомянуто здесь: Получить имена столбцов и типы хранимых процедур?
Сначала создайте эту хранимую процедуру для генерации SQL для временного
CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
@ProcedureName nvarchar(128),
@TableName nvarchar(128),
@SQL nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('
SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +'' + ','
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID(@ProcedureName),
NULL
);
--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))
SET @SQL = @SQL +')'
Чтобы использовать процедуру, вызовите ее следующим образом:
DECLARE @SQL NVARCHAR(MAX)
exec dbo.usp_GetStoredProcTableDefinition
@ProcedureName='dbo.usp_YourProcedure',
@TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT
INSERT INTO ##YourGlobalTempTable
EXEC [dbo].usp_YourProcedure
select * from ##YourGlobalTempTable
Обратите внимание, что я использую глобальную временную таблицу. Это связано с тем, что использование EXEC для запуска динамического SQL создает собственный сеанс, поэтому обычная временная таблица выходит за рамки любого последующего кода. Если глобальная временная таблица является проблемой, вы можете использовать обычную временную таблицу, но любой последующий SQL должен быть динамическим, то есть также выполняемым оператором EXEC.
Quassnoi поставил меня большую часть пути туда, но одна вещь отсутствовала:
**** Мне нужно было использовать параметры в хранимой процедуре.****
И OPENQUERY не позволяет этому случиться:
Таким образом, я нашел способ работать с системой, а также не должен делать определение таблицы настолько жестким и переопределять его в другой хранимой процедуре (и, конечно, воспользоваться шансом, что она может сломаться)!
Да, вы можете динамически создать определение таблицы, возвращенное из хранимой процедуры, с помощью оператора OPENQUERY с фиктивными переменными (пока набор NO RESULT SET возвращает то же количество полей и в той же позиции, что и набор данных с хорошими данными).
После создания таблицы вы можете использовать хранимую процедуру exec во временной таблице в течение всего дня.
И чтобы отметить (как указано выше), вы должны включить доступ к данным,
EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE
Код:
declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime
set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()
--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.
select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')
set @locCompanyId = '7753231'
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
set @locCompanyId = '9872231'
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211
Спасибо за информацию, которая была предоставлена первоначально...Да, наконец, мне не нужно создавать все эти поддельные (строгие) определения таблиц при использовании данных из другой хранимой процедуры или базы данных, и да, вы также можете использовать параметры.
Поиск по ссылочным тегам:
Хранимая процедура SQL 2005 во временную таблицу
openquery с хранимой процедурой и переменными 2005
openquery с переменными
выполнить хранимую процедуру во временной таблице
Обновление: это не будет работать с временными таблицами, поэтому мне пришлось прибегнуть к созданию временной таблицы вручную.
Заметим, что это не будет работать с временными таблицами, http://www.sommarskog.se/share_data.html
Ссылка: Следующим шагом является определение LOCALSERVER. В примере это может выглядеть как ключевое слово, но на самом деле это только имя. Вот как вы это делаете:
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servername
Чтобы создать связанный сервер, вы должны иметь разрешение ALTER ANY SERVER или быть участником любой из фиксированных ролей сервера sysadmin или setupadmin.
OPENQUERY открывает новое соединение с SQL Server. Это имеет некоторые последствия:
Процедура, которую вы вызываете с помощью OPENQUERY, не может ссылаться на временные таблицы, созданные в текущем соединении.
Новое соединение имеет собственную базу данных по умолчанию (определенную с помощью sp_addlinkedserver, по умолчанию master), поэтому все спецификации объекта должны включать имя базы данных.
Если у вас есть открытая транзакция и вы удерживаете блокировки, когда вызываете OPENQUERY, вызываемая процедура не может получить доступ к тому, что вы заблокировали. То есть, если вы не будете осторожны, вы заблокируете себя.
Подключение не бесплатное, поэтому снижается производительность.
Если вам повезло иметь SQL 2012 или выше, вы можете использовать dm_exec_describe_first_result_set_for_object
Я только что отредактировал sql, предоставленный gotqn. Спасибо, получил.
Это создает глобальную временную таблицу с именем, совпадающим с именем процедуры. Временная таблица может позже использоваться по мере необходимости. Только не забудьте бросить его перед повторным выполнением.
declare @procname nvarchar(255) = 'myProcedure',
@sql nvarchar(max)
set @sql = 'create table ##' + @procname + ' ('
begin
select @sql = @sql + '[' + r.name + '] ' + r.system_type_name + ','
from sys.procedures AS p
cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
where p.name = @procname
set @sql = substring(@sql,1,len(@sql)-1) + ')'
execute (@sql)
execute('insert ##' + @procname + ' exec ' + @procname)
end
Этот сохраненный процесс выполняет работу:
CREATE PROCEDURE [dbo].[ExecIntoTable]
(
@tableName NVARCHAR(256),
@storedProcWithParameters NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @driver VARCHAR(10)
DECLARE @connectionString NVARCHAR(600)
DECLARE @sql NVARCHAR(MAX)
DECLARE @rowsetSql NVARCHAR(MAX)
SET @driver = '''SQLNCLI'''
SET @connectionString =
'''server=' +
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) +
COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') +
';trusted_connection=yes'''
SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''
SET @sql = '
SELECT
*
INTO
' + @tableName + '
FROM
OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'
EXEC (@sql)
END
GO
Это небольшая переделка: вставьте результаты хранимой процедуры в таблицу, чтобы она действительно работала.
Если вы хотите, чтобы он работал с временной таблицей, вам нужно будет использовать ##GLOBAL стол и бросьте его потом.
Чтобы вставить первый набор записей хранимой процедуры во временную таблицу, вам необходимо знать следующее:
- во временную таблицу может быть вставлен только первый набор строк хранимой процедуры
- хранимая процедура не должна выполнять динамический оператор T-SQL (
sp_executesql) - сначала нужно определить структуру временной таблицы
Вышесказанное может выглядеть как ограничение, но ИМХО это прекрасно имеет смысл - если вы используете sp_executesql один раз вы можете вернуть два столбца и один раз десять, а если у вас есть несколько наборов результатов, вы также не можете вставить их в несколько таблиц - вы можете вставить максимум в две таблицы в одном выражении T-SQL (используя OUTPUT оговорка и никаких триггеров).
Таким образом, проблема заключается в том, как определить структуру временной таблицы перед выполнением EXEC ... INTO ... заявление.
- sys.dm_exec_describe_first_result_set_for_object
- sys.dm_exec_describe_first_result_set
- sp_describe_first_result_set
Первый работает с OBJECT_ID в то время как второй и третий работают также со специальными запросами. Я предпочитаю использовать DMV вместо SP, как вы можете использовать CROSS APPLY и создать временные определения таблиц для нескольких процедур одновременно.
SELECT p.name, r.*
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;
Также обратите внимание на system_type_name поле, как это может быть очень полезным. В нем хранится полное определение столбца. Например:
smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)
и вы можете использовать его напрямую в большинстве случаев для создания определения таблицы.
Итак, я думаю, что в большинстве случаев (если хранимая процедура соответствует определенным критериям) вы можете легко построить динамические операторы для решения таких проблем (создать временную таблицу, вставить в нее результат хранимой процедуры, сделать то, что вам нужно с данными),
Обратите внимание, что вышеприведенные объекты не могут определить данные первого результирующего набора в некоторых случаях, например, когда выполняются динамические операторы T-SQL или временные таблицы используются в хранимой процедуре.
- Я создаю таблицу со следующей схемой и данными.
- Создайте хранимую процедуру.
Теперь я знаю, каков результат моей процедуры, поэтому я выполняю следующий запрос.
CREATE TABLE [dbo].[tblTestingTree]( [Id] [int] IDENTITY(1,1) NOT NULL, [ParentId] [int] NULL, [IsLeft] [bit] NULL, [IsRight] [bit] NULL, CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[tblTestingTree] ON INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo].[tblTestingTree] OFFЗНАЧЕНИЯ (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo].[TblTestingTree] Вкл.
create procedure GetDate as begin select Id,ParentId from tblTestingTree end create table tbltemp ( id int, ParentId int ) insert into tbltemp exec GetDate select * from tbltemp;
Если запрос не содержит параметра, используйте OpenQuery еще использовать OpenRowset,
Основная вещь будет состоять в том, чтобы создать схему согласно хранимой процедуре и вставить в эту таблицу. например:
DECLARE @abc TABLE(
RequisitionTypeSourceTypeID INT
, RequisitionTypeID INT
, RequisitionSourcingTypeID INT
, AutoDistOverride INT
, AllowManagerToWithdrawDistributedReq INT
, ResumeRequired INT
, WarnSupplierOnDNRReqSubmission INT
, MSPApprovalReqd INT
, EnableMSPSupplierCounterOffer INT
, RequireVendorToAcceptOffer INT
, UseCertification INT
, UseCompetency INT
, RequireRequisitionTemplate INT
, CreatedByID INT
, CreatedDate DATE
, ModifiedByID INT
, ModifiedDate DATE
, UseCandidateScheduledHours INT
, WeekEndingDayOfWeekID INT
, AllowAutoEnroll INT
)
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc
Код
CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 NCHAR(50) NOT NULL,
col3 TEXT NOT NULL,
col4 DATETIME NULL,
col5 NCHAR(50) NULL,
col6 CHAR(2) NULL,
col6 NCHAR(100) NULL,
col7 INT NULL,
col8 NCHAR(50) NULL,
col9 DATETIME NULL,
col10 DATETIME NULL
)
DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)
SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'
INSERT INTO #T1
(
col1,
col2,
col3,
col4,
col5,
col6,
col6,
col7,
col8,
col9,
col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6
Надеюсь, это поможет. Пожалуйста, квалифицируйтесь как нужно.
Я нашел Передачу массивов / таблиц данных в хранимые процедуры, которые могут дать вам другое представление о том, как вы можете решить свою проблему.
Ссылка предлагает использовать параметр типа изображения для передачи в хранимую процедуру. Затем в хранимой процедуре изображение преобразуется в табличную переменную, содержащую исходные данные.
Может быть, есть способ, которым это можно использовать с временной таблицей.
Я столкнулся с той же проблемой, и вот что я сделал для этого по предложению Павла. Основная часть здесь заключается в использовании NEWID() чтобы избежать одновременного запуска нескольких процедур / сценариев хранилища несколькими пользователями, это затрудняет глобальную временную таблицу.
DECLARE @sql varchar(max) = '',
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)
EXEC('SELECT * FROM [' + @tmp_global_table + ']')
Другой метод заключается в создании типа и использовании PIPELINED для последующей передачи вашего объекта. Однако это ограничено знанием столбцов. Но у него есть преимущество:
SELECT *
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))
Это можно сделать в SQL Server 2014+, если SP возвращает только одну таблицу. Если кто-нибудь найдет способ сделать это для нескольких столов, я бы хотел узнать об этом.
DECLARE @storeProcname NVARCHAR(MAX) = ''
SET @storeProcname = 'myStoredProc'
DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '
SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'
EXEC (@strSQL)
INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')
SELECT * FROM myTableName
DROP TABLE myTableName
Это извлекает определение возвращаемой таблицы из системных таблиц и использует его для создания временной таблицы. Затем вы можете заполнить его из SP, как указано выше.
Существуют также варианты этого, которые также работают с динамическим SQL.
После поиска я нашел способ динамически создавать временную таблицу для любой хранимой процедуры без использования OPENROWSET или OPENQUERY использование общей схемы определения результата хранимой процедуры, особенно если вы не являетесь администратором базы данных.
Сервер sql имеет встроенную процедуру sp_describe_first_result_setкоторый может предоставить вам схему любого набора результатов процедур. Я создал таблицу схемы на основе результатов этой процедуры и вручную установил для всех полей значение NULLABLE.
declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)
-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden bit NULL, column_ordinal int NULL, name sysname NULL, is_nullable bit NULL, system_type_id int NULL, system_type_name nvarchar(256) NULL,
max_length smallint NULL, precision tinyint NULL, scale tinyint NULL, collation_name sysname NULL, user_type_id int NULL, user_type_database sysname NULL,
user_type_schema sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name nvarchar(4000),xml_collection_id int NULL,xml_collection_database sysname NULL,
xml_collection_schema sysname NULL,xml_collection_name sysname NULL,is_xml_document bit NULL,is_case_sensitive bit NULL,is_fixed_length_clr_type bit NULL,
source_server sysname NULL,source_database sysname NULL,source_schema sysname NULL,source_table sysname NULL,source_column sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key bit NULL,is_updateable bit NULL,is_computed_column bit NULL,is_sparse_column_set bit NULL,ordinal_in_order_by_list smallint NULL,
order_by_list_length smallint NULL,order_by_is_descending smallint NULL,tds_type_id int NULL,tds_length int NULL,tds_collation_id int NULL,
tds_collation_sort_id tinyint NULL)
-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0
-- Create a query to generate and populate a global temp table from above results
select
@qry = 'Create table ##t(' +
stuff(
(select ',' + name + ' '+ system_type_name + ' NULL'
from #d d For XML Path, TYPE)
.value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')
insert into ##t
Exec '+@procname+' ' + @param
Exec sp_executesql @qry
-- Use below global temp table to query the data as you may
select * from ##t
-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d
Разработан и протестирован на версии Sql Server - Microsoft SQL Server 2016 (RTM) - 13.0.1601.5(сборка 17134:)
Вы можете настроить схему для своей версии SQL-сервера, которую вы используете (при необходимости).
Это простой двухэтапный процесс: - создать временную таблицу - вставить во временную таблицу.
Код для выполнения такой же:
CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;
Если вам известны параметры, которые передаются, и если у вас нет доступа к программе sp_configure, то отредактируйте хранимую процедуру с этими параметрами, и ее можно сохранить в глобальной таблице ##.
Несколько лет опоздали с вопросом, но мне нужно было что-то вроде этого для быстрой и грязной генерации кода. Я считаю, что, как утверждали другие, проще определить временную таблицу заранее, но этот метод должен работать для простых запросов хранимых процедур или отчетов sql.
Это будет немного запутанно, но оно заимствовано у участников, а также у решения Пола Уайта из DBA Stack Exchange Get stored procedure result column-types. Опять же, повторюсь, этот подход и пример не предназначен для процессов в многопользовательской среде. В этом случае определение таблицы на короткое время устанавливается в глобальной временной таблице для справки посредством процесса шаблона генерации кода.
Я не полностью тестировал это, поэтому могут быть оговорки, поэтому вы можете перейти по ссылке MSDN в ответе Пола Уайта. Это относится к SQL 2012 и выше.
Сначала используйте хранимую процедуру sp_describe_first_result_set, которая напоминает описание Oracle.
Это оценит первую строку первого набора результатов, поэтому, если ваша хранимая процедура или инструкция возвращает несколько запросов, она будет описывать только первый результат.
Я создал хранимую процедуру, чтобы разбить задачи, которые возвращают одно поле для выбора для создания определения временной таблицы.
CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
@sql NVARCHAR(4000)
,@table_name VARCHAR(100)
,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempTableDefinition NVARCHAR(MAX)
DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)
DECLARE @ResultDefinition TABLE ( --The View Definition per MSDN
is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, [name] sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, [precision] tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_is_descending smallint NULL
, order_by_list_length smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL
)
--Insert the description into table variable
INSERT @ResultDefinition
EXEC sp_describe_first_result_set @sql
--Now Build the string to create the table via union select statement
;WITH STMT AS (
SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
UNION ALL
SELECT
CONCAT(
CASE column_ordinal
WHEN 1 THEN ' ' ELSE ' , ' END --Determines if comma should precede
, QUOTENAME([name]) , ' ', system_type_name -- Column Name and SQL TYPE
,CASE is_nullable
WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END --NULLABLE CONSTRAINT
) AS TextVal
FROM @ResultDefinition WHERE is_hidden = 0 -- May not be needed
UNION ALL
SELECT N');' + @NewLine
)
--Now Combine the rows to a single String
SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT
SELECT @TableDefinition = @TempTableDefinition
END
Загадка заключается в том, что вам нужно использовать глобальную таблицу, но вам нужно сделать ее достаточно уникальной, чтобы вы могли часто бросать и создавать из нее, не беспокоясь о столкновении.
В примере я использовал Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) для глобальной переменной, заменив дефисы на подчеркивание.
DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'
--@sql can be a stored procedure name like dbo.foo without parameters
DECLARE @TableDef NVARCHAR(MAX)
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
@sql, @GlobalTempTable, @TableDef OUTPUT
--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef
--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @sql
--Select the results into your undefined Temp Table from the Global Table
SELECT *
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
SELECT * FROM #MyTempTable
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
Опять же, я тестировал его только с простыми запросами хранимых процедур и простыми запросами, поэтому ваш опыт может отличаться. Надеюсь, это кому-то поможет.
Вот мой T-SQL с параметрами
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
DECLARE @param1 int = 1, @param2 int = 2
DECLARE @SQLStr varchar(max) = 'SELECT * INTO #MyTempTable
FROM OPENROWSET(''SQLNCLI'',
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec StoredProcedureName '+ CAST(@param1 AS varchar(15)) +','+ CAST(@param2 AS varchar(15)) +''') AS a ;
select * from #MyTempTable;
drop table #MyTempTable
';
EXECUTE(@SQLStr);
Если вы позволите динамическому SQL создать временную таблицу, эта таблица будет принадлежать соединению динамического SQL, а не соединению, из которого вызывается ваша хранимая процедура.
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
SELECT * FROM #Pivoted;
Сообщение 208, уровень 16, состояние 0 Недопустимое имя объекта "#Pivoted". Это потому, что #Pivoted принадлежит соединению Dynamic SQL. Итак, последняя инструкция
SELECT * FROM #Pivoted
терпит неудачу.
Один из способов не столкнуться с этой проблемой - убедиться, что все ссылки на #Pivoted сделаны из самого динамического запроса:
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
Старый пост, но полезный. В дополнение к другим ответам можно также записать вывод хранимой процедуры в табличную переменную (поскольку спрашивающий хочет избежать использования #temp-table), поэтому я создал полностью рабочий пример с использованием @table-variable:
--Note: separately run each section at a time:
--Section-1: create stored proc that outputs a result-set:
CREATE OR ALTER PROCEDURE TestSP AS
BEGIN
SELECT database_id, name from sys.databases
END
------------------------
--Section-2: create @table-variable with columns matching the output (alternatively #Temp table can be used):
DECLARE @t TABLE (did INT, dname VARCHAR(99))
--Capture the output:
insert into @t
exec TestSP
--View the captured output:
select * from @t
------------------------
--Section-3: Clean-up
DROP PROCEDURE TestSP
ХТН.
Что ж, вам нужно создать временную таблицу, но у нее не должно быть правильной схемы.... Я создал хранимую процедуру, которая изменяет существующую временную таблицу, чтобы в ней были необходимые столбцы с правильными данными. тип и порядок (удаление всех существующих столбцов, добавление новых столбцов):
GO
create procedure #TempTableForSP(@tableId int, @procedureId int)
as
begin
declare @tableName varchar(max) = (select name
from tempdb.sys.tables
where object_id = @tableId
);
declare @tsql nvarchar(max);
declare @tempId nvarchar(max) = newid();
set @tsql = '
declare @drop nvarchar(max) = (select ''alter table tempdb.dbo.' + @tableName
+ ' drop column '' + quotename(c.name) + '';''+ char(10)
from tempdb.sys.columns c
where c.object_id = ' +
cast(@tableId as varchar(max)) + '
for xml path('''')
)
alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
exec sp_executeSQL @drop;
declare @add nvarchar(max) = (
select ''alter table ' + @tableName
+ ' add '' + name
+ '' '' + system_type_name
+ case when d.is_nullable=1 then '' null '' else '''' end
+ char(10)
from sys.dm_exec_describe_first_result_set_for_object('
+ cast(@procedureId as varchar(max)) + ', 0) d
order by column_ordinal
for xml path(''''))
execute sp_executeSQL @add;
alter table ' + @tableName + ' drop column ' + quotename(@tempId) + ' ';
execute sp_executeSQL @tsql;
end
GO
create table #exampleTable (pk int);
declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')
exec #TempTableForSP @tableId, @procedureId;
insert into #exampleTable
exec examplestoredProcedure
Обратите внимание, что это не сработает, если sys.dm_exec_describe_first_result_set_for_object не может определить результаты хранимой процедуры (например, если она использует временную таблицу).