[SQL]讓 Execute 可以搭配 Select Into，而不再只有 Insert into

2015-02-02

34188
0
SQL

讓 Execute 或是 SP 的結果，可以搭配 Select Into，而不再只有 Insert into

記得之前同事問一個問題，在 EXEC 裡建立的 temp table 後，為何在外面的用不到呢?

因為 Scope 問題，外面並不知道 EXEC 裡面建立的 temp table 或是做了什麼事，如下面的Script，結果是什麼呢?

use master;
go
 
EXEC('use TSQL2012;')
 
-- TSQL2012 DB 才有的Table 
SELECT empid, lastname 
FROM HR.Employees;

所以一般來說，如果我們需要用一個 Temp Table 去接 EXEC 的結果，需要先建立這個 Temp Table。

然後使用 Insert into (在外面或是在Exec裡面用) ，如下，

USE tempdb
GO
--建立測試的資料表 myTable
CREATE TABLE myTable
(c1 INT
, c2 VARCHAR(30)
, c3 NVARCHAR(30)
);
GO

INSERT INTO myTable(c1, c2, c3) VALUES(1, 'c2 value', N'c3 value 堃');
INSERT INTO myTable(c1, c2, c3) VALUES(2, 'c2-2 value', N'c3-2 value 堃');
SELECT * FROM dbo.myTable;

--先建立 暫存資料表
CREATE TABLE #myTempTable_1
(c1 INT
, c2 VARCHAR(30)
, c3 NVARCHAR(30)
);
GO

-- 使用 Insert Into 
INSERT INTO #myTempTable_1
EXEC('SELECT * FROM myTable;');

--或是在 EXEC 裡使用 Insert into
EXEC('INSERT INTO #myTempTable_1 SELECT * FROM myTable;');

SELECT * FROM #myTempTable_1;

DROP TABLE #myTempTable_1;

但是如果這個欄位的資訊只有 EXEC 裡才知道呢? 我不想要一開始就建立暫存的資料表呢?

那我們可以參考「Insert results of a Stored Procedure into a Temporary Table」，

透過 OPENQUERY 來達到這個目的，但是 OPENQUERY 需要 LinkedServer ，所以要建立連接自已的 LinkedServer ，如下，

--建立一個 LinkedServer 連自已
sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

--透過 Linked Server 來取得 myTable 的資料 (linkedserver.db.schema.table)
SELECT * FROM LOCALSERVER.tempdb.dbo.myTable;

建立好 LinkedServer 後，就可以利用 OPENQUERY 來執行 Execute SQL，如下，

--將Execute的結果建立到暫存的資料表 #tempTable
SELECT  *
INTO    #myTempTable
FROM    OPENQUERY(LOCALSERVER, 'EXEC(''SELECT * FROM LOCALSERVER.tempdb.dbo.myTable;'')');

--取得 #tempTable 的資料
SELECT * FROM #myTempTable;

-- clean up
DROP TABLE #myTempTable;