[sql] ストアドプロシージャの結果を一時テーブルに挿入する


Answers

テンポラリ・テーブルを最初に宣言せずに実行する場合は、 ストアド・プロシージャではなくユーザ定義関数を作成し、そのユーザ定義関数がテーブルを返すようにすることができます。 代わりに、ストアドプロシージャを使用する場合は、次のように試してください:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
Question

SELECT * INTO [temp table] FROM [stored procedure]を実行するにはどうすればよいですか? FROM [Table]からではなく、 [temp table]を定義しないで[temp table]

BusinessLine tmpBusLineからすべてのデータをSelectしてtmpBusLine動作するようにします。

select *
into tmpBusLine
from BusinessLine

私は同じをしようとしているが、データを返すstored procedureを使用して、全く同じではありません。

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

出力メッセージ:

メッセージ156、レベル15、状態1、行2 'exec'というキーワードの構文が正しくありません。

私は出力ストアドプロシージャと同じ構造を持つ一時テーブルを作成するいくつかの例を読んできましたが、これはうまくいきますが、列を指定しないといいでしょう。




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;



コード

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

私はこれが役立つことを願っています 適切な資格を与えてください。




格納されたprocの結果テーブルが複雑すぎて、 "create table"文を手作業で入力し、OPENQUERYまたはOPENROWSETを使用できない場合は、sp_helpを使用してカラムとデータ型のリストを生成できます。 列のリストを取得したら、必要に応じて書式を設定するだけです。

ステップ1: "#temp"を出力クエリに追加します(例えば "[...]"から#tempに[...]を選択します)。

最も簡単な方法は、procの出力クエリを直接編集することです。 ストアドプロシージャを変更できない場合は、その内容を新しいクエリーウィンドウにコピーし、そのクエリーを変更することができます。

手順2:tempテーブルでsp_helpを実行します。 (例: "exec tempdb..sp_help #temp")

一時表を作成した後、temp表でsp_helpを実行して、varcharフィールドのサイズを含む列とデータ型のリストを取得します。

手順3:データ列と型をcreate table文にコピーする

私は "create table"ステートメントにsp_helpの出力をフォーマットするために使用するExcelシートを持っています。 あなたは奇妙なことは必要ありません。ただコピーしてSQLエディタに貼り付けるだけです。 ストアドプロシージャの結果をINSERTするために使用できる "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 temp)に変換するためにも使用できます。 これは、 create tableステートメントを自分でcreate tableよりも多くのステップがありますが、大きなプロセスでタイプミスやデータ型の不一致などの手動エラーが発生することはありません。 入力ミスをデバッグすると、最初にクエリを書くよりも時間がかかることがあります。




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'




  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
    



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ステートメントによっても実行されます。




これは、少し修正されたバージョンの質問に対する回答です。 ユーザー定義関数のストアドプロシージャの使用を中止することができれば、インラインテーブル値のユーザー定義関数を使用できます。 これは基本的に結果セットとしてテーブルを返すストアドプロシージャ(パラメータを取る)です。 したがって、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

私はこれが望ましい結果を得るための最も「ハックレス」な方法だと思います。 それは、追加の合併症なしに使用されることを意図した既存の機能を使用します。 インラインテーブル値のユーザー定義関数をストアドプロシージャにネストすることにより、2つの方法でその機能にアクセスできます。 プラス! 実際のSQLコードにはメンテナンスのポイントが1つしかありません。

OPENROWSETの使用が推奨されていますが、これはOPENROWSET関数が(Books Onlineから)使用するためのものではありません。

OLE DBデータソースからリモートデータにアクセスするために必要なすべての接続情報が含まれます。 この方法は、リンクサーバーのテーブルにアクセスする代わりの方法で、OLE DBを使用してリモートデータに接続してアクセスするための1回限りの特別な方法です。 OLE DBデータソースへの参照を頻繁に参照するには、リンクサーバーを使用してください。

OPENROWSETを使用するとジョブは終了しますが、ローカル接続を開いてデータをマーシャリングするためのオーバーヘッドが発生します。 また、セキュリティ上のリスクがあり、望ましくない場合があるアドホッククエリのアクセス許可が必要なため、オプションではない可能性があります。 また、OPENROWSETアプローチは、複数の結果セットを返すストアドプロシージャの使用を排除します。 1つのストアドプロシージャに複数のインラインテーブル値のユーザ定義関数をラップすることで、これを実現できます。




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.




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



最も簡単なソリューション:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

スキーマがわからない場合は、次の操作を実行できます。 この方法では深刻なセキュリティリスクがあることに注意してください。

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



あなたがSQL 2012以上を持つことができるほど幸運であれば、 dm_exec_describe_first_result_set_for_object

私はちょうどgotqnによって提供されたSQLを編集しました。 ありがとうございました。

これにより、プロシージャ名と同じ名前のグローバル一時表が作成されます。 後で必要に応じてテンポラリテーブルを使用できます。 再実行する前に削除することを忘れないでください。

    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



Links