ストアドプロシージャの結果からテーブルを生成する - SELECT INTO & OPENROWSET

ストアドプロシージャの結果セットを持つテーブルを生成したい。

ストアドプロシージャが返す結果からテーブルを作成して、他のスクリプトで使いたいような時ありますよね。

ここでは、ストアドプロシージャが返す結果を持つテーブルを作成する方法をご紹介します。


ストアドプロシージャの結果からテーブルを生成する

ストアドプロシージャから返ってくる結果セットのスキーマがわからない場合や、事前にテーブルを生成したくない場合は、SELECT INTO & OPENROWSET を使うことによって、ストアドプロシージャが返す結果セットを持つテーブルを生成することができます。


Ad Hoc Distributed Queries オプションを有効化する

まず OPENROWSET を使うために、Ad Hoc Distributed Queries オプションを有効化します。 

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO

ストアドプロシージャの結果からテーブルを生成する 1

このオプションを有効化していないと実行すると次のようなエラーになります。

ストアドプロシージャの結果からテーブルを生成する 2

SELECT * INTO [テーブル名] FROM OPENROWSET

それでは、OPENROWSET を使ってストアドプロシージャが返す値を元に、テーブルを生成してみましょう。

次のような、テーブル名を渡すと、カラムを返すストアドプロシージャ uspColumnListGet があります。 

CREATE PROCEDURE uspColumnListGet
    @TableName NVARCHAR(128)
AS 
BEGIN

    SElECT      ORDINAL_POSITION,
                COLUMN_NAME,
                DATA_TYPE
    FROM        INFORMATION_SCHEMA.COLUMNS
    WHERE       TABLE_NAME = @TableName
    ORDER BY    ORDINAL_POSITION;

END

ストアドプロシージャの結果からテーブルを生成する 1

ストアドプロシージャの結果からテーブルを生成する 2


Windows 認証で、デフォルトインスタンスのデータベースサーバの Test データベース に作った uspColumnListGet から、 OPENROWSET データを取得し、#ColumnList2 という名前の一時テーブルを作るクエリーは以下の通りです。 

SELECT  * 
INTO    #ColumnList2
FROM    OPENROWSET( 'SQLNCLI', 
                    'Server=.\;Trusted_Connection=yes;',
                    'EXEC Test.dbo.uspColumnListGet ''Users''') AS T;

SELECT  *
FROM    #ColumnList2;

ストアドプロシージャの結果からテーブルを生成する 3

SQL サーバー認証を使いたいときは、次のようにユーザー名とパスワードを指定することができます。 

SELECT  * 
INTO    #ColumnList2
FROM    OPENROWSET( 'SQLNCLI', 
                    'Server=.\;UID=Tomoko;PWD=xxxxxxx;',
                    'EXEC Test.dbo.uspColumnListGet ''Users''') AS T;

OPENROWSET は引数に変数を使えない

残念ながら OPENROWSET は引数に変数がつかえないため、uspColumnListGet に渡す @TableName をパラメター可したい時は、工夫が必要です。

次のスクリプトは、sp_executesql で、生成した OPENROWSET を含む T-SQL ステートメントを実行して、uspColumnListGet の返す結果セットを持つ ##ColumnList3 テーブルを生成しています。 

DECLARE @TableName NVARCHAR(128) = 'Users';

IF OBJECT_ID(N'tempdb..##ColumnList3', N'U') IS NOT NULL
DROP TABLE ##ColumnList3;

DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'SELECT  * 
            INTO    ##ColumnList3 
            FROM    OPENROWSET(''SQLNCLI'',
                               ''Server=.\;Trusted_Connection=yes;'',
                               ''EXEC Test.dbo.uspColumnListGet ' + @TableName + ''') AS T';

EXEC sp_executesql @Sql;

SELECT  *
FROM    ##ColumnList3;


DROP TABLE ##ColumnList3;

ストアドプロシージャの結果からテーブルを生成する 4

緑で囲まれた部分ですが、sp_executesql へもパラメーターとして渡せないので、ただ文字列をつなげています。 @TableName の値のバリデーションは、ここに到達するまでに済ませている前提です。

オレンジで囲まれた部分は、sp_executesql で作られた、# がひとつのローカル一時テーブルにはアクセスできないので ## がふたつのグローバルの一時テーブルを生成しています。

生成したステートメントを sp_executesql で実行する前に、青で囲まれた部分で ##ColumnList3 という名前のグローバル一時テーブルが存在する場合はテーブルを削除しています。


複雑になるので、ストアドプロシージャが返す結果セットのスキーマがわかっている場合は、事前にテーブルを作って INSERT INTO & EXEC で実行するほうが良いと思います。

詳しくはこちらをご覧ください。
ストアドプロシージャの結果をテーブルに挿入する

© 2010-2024 SQL Server 入門