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





12 Answers

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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
結果セット 戻り値 レコードセット

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'というキーワードの構文が正しくありません。

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




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




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

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

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

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



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;



格納された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よりも多くのステップがありますが、大きなプロセスではタイプミスやデータ型の不一致などの手動エラーが発生しません。 入力ミスをデバッグすると、最初にクエリを書くよりも時間がかかることがあります。




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




あなたが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



  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
    

    VALUES(10、5、1、NULL)SET IDENTITY_INSERT [dbo]。[tblTestingTree]オン

    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;
    



クエリにパラメータが含まれていない場合は、 OpenQueryを使用しOpenRowset 。それ以外の場合はOpenRowset使用し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



別の方法は、型を作成し、PIPELINEDを使用してオブジェクトを戻す方法です。ただし、これは列を知ることに限られます。しかし、それはできることの利点があります:

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



渡されるパラメータがわかっていて、sp_configureを作成するためのアクセス権がない場合は、これらのパラメータを使用してストアドプロシージャを編集し、##グローバルテーブルに格納することができます。




SQL Server 2014以降では、SPは1つのテーブルのみを返します。誰かが複数のテーブルに対してこれを行う方法を見つけたら、私はそれについて知りたいです。

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

これは、システムテーブルから返されたテーブルの定義を引き出し、それを使って一時テーブルを構築します。その後、前述のようにSPから入力することができます。

また、動的SQLで動作するこのバリエーションもあります。




Related