with - stored procedure sql server




將存儲過程的結果插入臨時表中 (16)

我如何做一個SELECT * INTO [temp table] FROM [stored procedure] ? 不是FROM [Table]並且沒有定義[temp table]

SelectBusinessLinetmpBusLine所有數據tmpBusLine可以正常工作。

select *
into tmpBusLine
from BusinessLine

我嘗試的是相同的,但使用返回數據的stored procedure並不完全相同。

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

輸出消息:

Msg 156,Level 15,State 1,Line 2關鍵字'exec'附近的語法錯誤。

我已經閱讀了幾個創建與輸出存儲過程相同的結構的臨時表的示例,該示例工作正常,但不提供任何列是很好的。


簡單的解決方案:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

如果您不知道架構,那麼您可以執行以下操作。 請注意,這種方法存在嚴重的安全風險。

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

  1. 我正在創建一個包含以下模式和數據的表格。
  2. 創建一個存儲過程。
  3. 現在我知道我的過程的結果是什麼,所以我正在執行以下查詢。

    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
    
    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
    

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

我希望這有幫助。 請適當的資格。


Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))

I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.


你可以使用OPENROWSET 。 看一看。 如果尚未啟用Ad Hoc分佈式查詢,我還包含sp_configure代碼以啟用Ad Hoc分佈式查詢。

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

在SQL Server 2005中,您可以使用INSERT INTO ... EXEC將存儲過程的結果插入到表中。 從MSDN的INSERT文檔 (對於SQL Server 2000,實際上):

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

如果你想在不首先聲明臨時表的情況下執行該操作,則可以嘗試創建用戶定義的函數而不是存儲過程 ,並使該用戶定義的函數返回一個表。 或者,如果您想使用存儲過程,請嘗試如下所示:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

如果存儲過程的結果表過於復雜,無法手工輸入“create table”語句,並且無法使用OPENQUERY或OPENROWSET,則可以使用sp_help為您生成列和數據類型列表。 一旦你有列的列表,這只是一個格式化它的問題,以滿足您的需求。

第1步:將“輸入到#temp”添加到輸出查詢中(例如“從[...]中選擇[#temp]”)。

最簡單的方法是直接在proc中編輯輸出查詢。 如果您不能更改存儲的proc,則可以將內容複製到新的查詢窗口中,並在那裡修改查詢。

第2步:在臨時表上運行sp_help。 (例如“exec tempdb..sp_help #temp”)

創建臨時表之後,在臨時表上運行sp_help以獲取包含varchar字段大小的列和數據類型的列表。

第3步:將數據列和類型複製到create table語句中

我有一張Excel工作表,用於將sp_help的輸出格式化為“create table”語句。 你不需要任何特別的東西,只需複制並粘貼到你的SQL編輯器中即可。 使用列名稱,大小和類型來構造“Create table #x [...]”或“declare @x table [...]”語句,您可以使用它來插入存儲過程的結果。

第4步:插入新創建的表格

現在,您將擁有一個與本主題中介紹的其他解決方案類似的查詢。

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

此技術也可用於將臨時表( #temp )轉換為表變量( @temp )。 雖然這可能比僅僅編寫create table語句更多的步驟,但它可以防止大進程中的手動錯誤,如拼寫錯誤和數據類型不匹配。 調試錯字可能比首先編寫查詢需要更多的時間。


如果查詢不包含參數,則使用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

您的存儲過程是否只檢索數據或進行修改? 如果僅用於檢索,則可以將存儲過程轉換為函數並使用公用表表達式(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)

為了將存儲過程的第一個記錄集插入臨時表,您需要知道以下內容:

  1. 只有存儲過程的第一行可以插入臨時表中
  2. 存儲過程不得執行動態T-SQL語句( sp_executesql
  3. 您需要首先定義臨時表的結構

上述可能看起來是有限制的,但恕我直言,它完全有道理 - 如果您使用的是sp_executesql您可以一次返回兩列,一次返回兩列,如果您有多個結果集,則不能將它們插入多個表中 - 您可以插入在一個T-SQL語句中使用兩個表中的最大值(使用OUTPUT子句和無觸發器)。

所以,問題主要是如何在執行EXEC ... INTO ...語句之前定義臨時表結構。

第一個使用OBJECT_ID工作,第二個和第三個使用Ad-hoc查詢。 我傾向於使用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)

並且可以在大多數情況下直接使用它來創建表定義。

因此,我認為在大多數情況下(如果存儲過程符合特定條件),您可以輕鬆地構建動態語句來解決此類問題(創建臨時表,將存儲過程結果插入它,根據數據執行所需操作) 。

請注意,上述對像在某些cases無法定義第一個結果集數據,例如執行動態T-SQL語句或在存儲過程中使用臨時表時。


當存儲過程返回很多列並且您不想手動“創建”一個臨時表來保存結果時,我發現最簡單的方法是進入存儲過程,並在其上添加一個“into”子句最後選擇語句並將where = 1加1。

運行一次存儲過程,然後返回並刪除剛剛添加的SQL代碼。 現在,您將有一個空表匹配存儲過程的結果。 您可以將“將表格編寫為臨時表格”或直接將其插入該表格中。


這是對您的問題稍作修改的版本的答案。 如果您可以放棄使用用戶定義函數的存儲過程,則可以使用內聯表值用戶定義的函數。 這基本上是一個存儲過程(將帶參數),它返回一個表作為結果集; 因此將很好地與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方法將阻止使用返回多個結果集的存儲過程。 在單個存儲過程中包裝多個行內表值用戶定義的函數可以實現這一點。


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






stored-procedures