sql - 複数 - ストアドプロシージャ select 結果 格納




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

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

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

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

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

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

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


  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;
    

Quassnoiは私のところにある方法のほとんどを入れましたが、1つは欠けていました:

****ストアドプロシージャでパラメータを使用する必要がありました。****

そしてOPENQUERYはこれが起こるのを許さない:

だから私は、システムを動作させる方法を見つけたので、テーブル定義を非常に堅固にする必要はなく、別のストアドプロシージャ内で再定義する必要はありません。

はい、 NO RESULT SETが同じ数のフィールドを返し、データが良好なデータセットと同じ位置にある限り、偽の可変可能変数を含むOPENQUERY文を使用して、ストアドプロシージャから返されたテーブル定義を動的に作成できます。

テーブルが作成されると、execストアドプロシージャを一日のうちに一時テーブルに使用できます。

また、上記のように、データアクセスを有効にする必要があります。

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

コード:

declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
  'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

元々提供されていた情報をありがとうございました... はい、最終的に 、別のストアドプロシージャやデータベースからデータを使用するときに、 これらの偽の (厳密な)テーブル定義を作成する必要はなくはいもパラメータを使用できます。

参照タグを検索:

  • SQL 2005ストアドプロシージャを一時テーブル

  • ストアドプロシージャと変数によるオープンクエリ2005

  • 変数付きのオープンクエリ

  • ストアドプロシージャを一時テーブルに実行する

更新: これは一時テーブルでは動作しませんので、一時テーブルを手動で作成する必要がありました。

バマー通知 :これは一時テーブルでは機能しませんhttp://www.sommarskog.se/share_data.html#OPENQUERY

参考:次に、LOCALSERVERを定義します。 この例ではキーワードのように見えますが、実際は名前だけです。 これはあなたがそれを行う方法です:

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

リンクサーバーを作成するには、ALTER ANY SERVER権限を持っているか、固定サーバーロールsysadminまたはsetupadminのメンバーである必要があります。

OPENQUERYはSQL Serverへの新しい接続を開きます。 これにはいくつかの意味があります。

OPENQUERYを使用して呼び出すプロシージャは、現行接続で作成された一時表を参照できません。

新しい接続には独自のデフォルト・データベース(sp_addlinkedserverで定義され、デフォルトはmaster)があるため、すべてのオブジェクト指定にデータベース名を含める必要があります。

開いているトランザクションがあり、OPENQUERYを呼び出すときにロックを保持している場合、呼び出されたプロシージャはロックしているものにアクセスできません。 つまり、あなたが慎重でない場合は、あなた自身をブロックするでしょう。

接続は無料ではないので、パフォーマンス上のペナルティがあります。


SQL Server 2005では、 INSERT INTO ... EXECを使用して、ストアドプロシージャの結果をテーブルに挿入できます。 MSDNのINSERTドキュメント (SQL Server 2000の場合):

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

コード

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

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


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

このストアドプロシージャはジョブを実行します:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

これを少し修正しました。実際に動作するように、 ストアドプロシージャの結果をテーブルに挿入します

一時テーブルを使用する場合は、 ##GLOBALテーブルを使用して後でドロップする必要があります。


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


クエリにパラメータが含まれていない場合は、 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

ストアドプロシージャが多くの列を返し、結果を保持するために一時的なテーブルを手動で作成したくない場合は、最も簡単な方法はストアドプロシージャに入り、 "into"句を最後にselect文を実行し、1 = 0をwhere句に追加します。

一度ストアドプロシージャを実行して、戻って、追加したばかりのSQLコードを削除してください。 これで、ストアドプロシージャの結果と一致する空のテーブルが作成されます。 テンポラリ・テーブルの場合は「create table as create」を実行するか、単にそのテーブルに直接挿入することもできます。


ストアド・プロシージャの最初のレコード・セットをテンポラリ・テーブルに挿入するには、次のことを知る必要があります。

  1. ストアドプロシージャの最初の行セットのみを一時テーブルに挿入することができます
  2. ストアドプロシージャは動的T-SQLステートメント( sp_executesql )を実行してはいけません。
  3. まず一時テーブルの構造を定義する必要があります

上記は制限として見えるかもしれませんが、完全に理にかなっていsp_executesqlを使用している場合は、一度2列と10回を返すことができます。複数の結果セットがある場合は、複数のテーブルに挿入することはできません。 1つのT-SQLステートメントで2つのテーブルの最大値( OUTPUT句とトリガなし)。

したがって、主にEXEC ... INTO ...ステートメントを実行する前に一時テーブル構造を定義する方法が問題です。

最初のものはOBJECT_ID動作し、2番目と3番目のものはアドホッククエリでも動作します。 私は、 CROSS APPLYを使用して複数のプロシージャの一時テーブル定義を同時に構築できるので、spの代わりにDMVを使用することを好みます。

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)

ほとんどの場合、これを直接使用してテーブル定義を作成することができます。

したがって、私は、ストアドプロシージャが特定の条件と一致する場合、そのような問題を解決するための動的ステートメントを簡単に作成できることを考えています(テンポラリテーブルの作成、ストアドプロシージャの結果の挿入、 。

上記のオブジェクトは、動的T-SQLステートメントが実行されるか、ストアドプロシージャで一時テーブルが使用されるような場合に、最初の結果セットデータを定義できないことに注意してください。


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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

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


私は次のことをするだろう

  1. UDF(テーブル値UDF)を作成(SPから変換)します。

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


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

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

私は同じ問題を抱えていました。ここで私がポールの提案からこれをしました。主要な部分は、ここでは、NEWID()複数のユーザーが同時にストアプロシージャ/スクリプトを実行するのを避けるために使用することです、グローバル一時テーブルの痛み。

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

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;




stored-procedures