Вопрос: Вставка результатов хранимой процедуры во временную таблицу


Как сделать 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'

Выходное сообщение:

Msg 156, уровень 15, состояние 1, строка 2   Неправильный синтаксис рядом с ключевым словом   'Exec'.

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


1318


источник


Ответы:


Вы можете использовать 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

613



Если вы хотите сделать это без предварительного объявления временной таблицы, вы можете попытаться создать определенную пользователем функцию, а не хранимая процедура и заставить эту пользовательскую функцию возвращать таблицу. Альтернативно, если вы хотите использовать хранимую процедуру, попробуйте что-то вроде этого:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

507



В SQL Server 2005 вы можете использовать INSERT INTO ... EXECдля вставки результата хранимой процедуры в таблицу. Из MSDN-х INSERTдокументация (для SQL Server 2000 фактически):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

263



Это ответ на слегка измененную версию вашего вопроса. Если вы можете отказаться от использования хранимой процедуры для пользовательской функции, вы можете использовать встроенную функцию, определяемую таблицей. Это, по сути, хранимая процедура (будет принимать параметры), которая возвращает таблицу в качестве результата; и поэтому будет удобно размещаться с инструкцией INTO.

Вот хороший быстрая статья на него и другие пользовательские функции. Если у вас все еще есть потребность во время хранения хранимой процедуры, вы можете обернуть встроенную функцию, определенную пользователем, с помощью хранимой процедуры. Хранимая процедура просто передает параметры при вызове select * из встроенной функции, определяемой пользователем.

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

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Затем вы можете вызвать эту функцию, чтобы получить то, что ваши результаты:

SELECT * FROM CustomersbyRegion(1)

Или сделать SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Если вам все еще нужна хранимая процедура, то заверните функцию как таковую:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я думаю, что это самый «безрадостный» метод для получения желаемых результатов. Он использует существующие функции, поскольку они предназначались для использования без дополнительных осложнений. Вложенная встроенная функция, определяемая пользователем, в хранимой процедуре, вы получаете доступ к функциям двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего предназначалась функция OPENROWSET (из книги в Интернете):

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

Использование OPENROWSET выполнит эту работу, но при этом возникнут дополнительные накладные расходы для открытия локальных соединений и сортировки данных. Он также может быть не во всех случаях, поскольку для него требуется специальное разрешение запроса, которое создает угрозу безопасности и, следовательно, может быть нежелательным. Кроме того, подход OPENROWSET исключает использование хранимых процедур, возвращающих более одного набора результатов. Это может обеспечить объединение нескольких встроенных функций, определяемых пользователем, в одну хранимую процедуру.


163



SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')

98



Самое простое решение:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Если вы не знаете схему, вы можете сделать следующее. пожалуйста обратите внимание, что в этом методе существуют серьезные риски безопасности.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

86



When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.

Run the stored procedure once and go back and remove the SQL code you just added. Now, you'll have an empty table matching the stored procedure's result. You could either "script table as create" for a temporary table or simply insert directly into that table.


79



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;

59



Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a with temp as ... and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.

For example,

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)

40