with - stored procedure sql server




Fügen Sie Ergebnisse einer gespeicherten Prozedur in eine temporäre Tabelle ein (16)

Einfachste Lösung:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Wenn Sie das Schema nicht kennen, können Sie Folgendes tun. Bitte beachten Sie, dass bei dieser Methode schwerwiegende Sicherheitsrisiken bestehen.

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

Wie mache ich eine SELECT * INTO [temp table] FROM [stored procedure] ? Nicht von FROM [Table] und ohne [temp table] ?

Select alle Daten aus BusinessLine in tmpBusLine funktioniert gut.

select *
into tmpBusLine
from BusinessLine

Ich versuche das gleiche, aber mit einer stored procedure , die Daten zurückgibt, ist nicht ganz dasselbe.

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

Ausgabenachricht:

Nachricht 156, Ebene 15, Status 1, Zeile 2 Falsche Syntax in der Nähe des Schlüsselwortes 'exec'.

Ich habe mehrere Beispiele zum Erstellen einer temporären Tabelle mit der gleichen Struktur wie die gespeicherte Ausgabeprozedur gelesen, die gut funktioniert, aber es wäre nett, keine Spalten zu liefern.


  1. Ich erstelle eine Tabelle mit dem folgenden Schema und den folgenden Daten.
  2. Erstellen Sie eine gespeicherte Prozedur.
  3. Jetzt weiß ich, was das Ergebnis meiner Prozedur ist, also führe ich die folgende Abfrage durch.

    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
    

Dieser gespeicherte Proc erledigt die Aufgabe:

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

Es ist eine kleine Überarbeitung von dies: Einfügen von gespeicherten Prozedur Ergebnisse in Tabelle, so dass es tatsächlich funktioniert.

Wenn Sie möchten, dass es mit einer temporären Tabelle arbeitet, müssen Sie eine Tabelle ##GLOBAL und sie anschließend ##GLOBAL .


Ich habe Passing Arrays / DataTables in Stored Procedures gefunden, die Ihnen eine andere Idee geben könnten, wie Sie Ihr Problem lösen könnten.

Die Verknüpfung schlägt vor, einen Bildtyp- Parameter zu verwenden, um in der gespeicherten Prozedur zu übergeben. In der gespeicherten Prozedur wird das Bild dann in eine Tabellenvariable umgewandelt, die die ursprünglichen Daten enthält.

Vielleicht gibt es eine Möglichkeit, wie dies mit einer temporären Tabelle verwendet werden kann.


Quassnoi hat mich den größten Teil dorthin gebracht, aber eine Sache hat gefehlt:

**** Ich musste Parameter in der gespeicherten Prozedur verwenden. ****

Und OPENQUERY lässt dies nicht zu:

So habe ich einen Weg gefunden, das System zu bearbeiten und auch die Tabellendefinition nicht so starr zu machen und in einer anderen gespeicherten Prozedur neu zu definieren (und natürlich die Chance zu nutzen, dass es kaputt geht)!

Ja, Sie können die aus der gespeicherten Prozedur zurückgegebene Tabellendefinition dynamisch erstellen, indem Sie die OPENQUERY-Anweisung mit gefälschten Variablen verwenden (solange das NO RESULT SET die gleiche Anzahl von Feldern und an derselben Position wie ein Dataset mit guten Daten zurückgibt).

Sobald die Tabelle erstellt wurde, können Sie die gespeicherte Prozedur exec den ganzen Tag lang in die temporäre Tabelle verwenden.

Und zu beachten (wie oben angegeben) müssen Sie den Datenzugriff aktivieren,

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

Code:

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

Danke für die Informationen, die ursprünglich zur Verfügung gestellt wurden ... Ja, schließlich muss ich nicht alle diese falschen (strikten) Tabellendefinitionen erstellen, wenn ich Daten aus einer anderen gespeicherten Prozedur oder Datenbank verwende, und ja, Sie können auch Parameter verwenden.

Referenztags suchen:

  • SQL 2005 gespeicherte Prozedur in temporäre Tabelle

  • openquery mit gespeicherter Prozedur und Variablen 2005

  • Openquery mit Variablen

  • Führen Sie die gespeicherte Prozedur in die temporäre Tabelle aus

Update: Dies funktioniert nicht mit temporären Tabellen, daher musste ich manuell auf die temporäre Tabelle zurückgreifen.

Bummer Anmerkung : Dies funktioniert nicht mit temporären Tabellen , http://www.sommarskog.se/share_data.html#OPENQUERY

Referenz: Als nächstes muss LOCALSERVER definiert werden. Es kann im Beispiel wie ein Schlüsselwort aussehen, aber es ist tatsächlich nur ein Name. Das ist wie man es macht:

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

Um einen Verbindungsserver zu erstellen, müssen Sie die Berechtigung ALTER ANY SERVER haben oder Mitglied einer der festen Serverrollen sysadmin oder setupadmin sein.

OPENQUERY öffnet eine neue Verbindung zu SQL Server. Dies hat einige Auswirkungen:

Die Prozedur, die Sie mit OPENQUERY aufrufen, kann temporäre Tabellen nicht verweisen, die in der aktuellen Verbindung erstellt werden.

Die neue Verbindung hat eine eigene Standarddatenbank (definiert mit sp_addlinkedserver, Standard ist Master), so dass alle Objektspezifikationen einen Datenbanknamen enthalten müssen.

Wenn Sie eine offene Transaktion haben und Sperren halten, wenn Sie OPENQUERY aufrufen, kann die aufgerufene Prozedur nicht auf das zugreifen, was Sie sperren. Das heißt, wenn Sie nicht aufpassen, werden Sie sich selbst blockieren.

Verbindung ist nicht kostenlos, es gibt also eine Leistungseinbuße.


Ruft Ihre gespeicherte Prozedur die Daten nur ab oder ändert sie auch? Wenn es nur zum Abrufen verwendet wird, können Sie die gespeicherte Prozedur in eine Funktion konvertieren und die Common Table Expressions (CTEs) verwenden, ohne sie wie folgt deklarieren zu müssen:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

Was immer aus dem CTE abgerufen werden muss, sollte jedoch nur in einer Anweisung verwendet werden. Sie können nicht with temp as ... und versuchen, es nach ein paar Zeilen von SQL zu verwenden. Sie können mehrere CTEs in einer Anweisung für komplexere Abfragen verwenden.

Beispielsweise,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

Um den ersten Datensatz einer gespeicherten Prozedur in eine temporäre Tabelle einzufügen, müssen Sie Folgendes wissen:

  1. Nur der erste Zeilensatz der gespeicherten Prozedur kann in eine temporäre Tabelle eingefügt werden
  2. Die gespeicherte Prozedur darf keine dynamische T-SQL-Anweisung ausführen ( sp_executesql )
  3. Sie müssen zuerst die Struktur der temporären Tabelle definieren

Das oben kann als Einschränkung aussehen, aber IMHO macht es vollkommen Sinn - wenn Sie sp_executesql , können Sie einmal zwei Spalten und einmal zehn zurückgeben, und wenn Sie mehrere Ergebnismengen haben, können Sie sie auch nicht in mehrere Tabellen einfügen - Sie können einfügen Maximum in zwei Tabellen in einer T-SQL-Anweisung (mit OUTPUT Klausel und ohne Trigger).

Das Problem besteht hauptsächlich darin, wie die temporäre Tabellenstruktur definiert wird, bevor die Anweisung EXEC ... INTO ... .

Der erste arbeitet mit OBJECT_ID der zweite und der dritte mit Ad-hoc-Abfragen. Ich bevorzuge die Verwendung des DMV anstelle von SP, da Sie CROSS APPLY und die Definitionen für temporäre Tabellen für mehrere Prozeduren gleichzeitig CROSS APPLY können.

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 auf das Feld system_type_name , da es sehr nützlich sein kann. Es speichert die vollständige Definition der Spalte. Beispielsweise:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

und Sie können es direkt in den meisten Fällen verwenden, um die Tabellendefinition zu erstellen.

Also, ich denke in den meisten Fällen (wenn die gespeicherte Prozedur bestimmte Kriterien erfüllt) können Sie leicht dynamische Anweisungen erstellen, um solche Probleme zu lösen (erstellen Sie die temporäre Tabelle, fügen Sie das Ergebnis der gespeicherten Prozedur ein, tun Sie, was Sie mit den Daten brauchen) .

Beachten Sie, dass die obigen Objekte in einigen cases nicht die ersten Resultsetdaten definieren, z. B. wenn dynamische T-SQL-Anweisungen ausgeführt oder temporäre Tabellen in der gespeicherten Prozedur verwendet werden.


Wenn Sie Glück haben, SQL 2012 oder höher zu haben, können Sie dm_exec_describe_first_result_set_for_object

Ich habe gerade das von Gotqn zur Verfügung gestellte sql bearbeitet. Danke Gotqn.

Dies erstellt eine globale temporäre Tabelle mit demselben Namen wie der Prozedurname. Die temporäre Tabelle kann später wie benötigt verwendet werden. Vergessen Sie nicht, es vor der erneuten Ausführung zu löschen.

    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

Wenn das OPENROWSET Ihnen Probleme verursacht, gibt es ab 2012 einen anderen Weg. Verwenden Sie sys.dm_exec_describe_first_result_set_for_object, wie hier erwähnt: Abrufen von Spaltennamen und Typen einer gespeicherten Prozedur?

Erstellen Sie zuerst diese gespeicherte Prozedur, um das SQL für das temporäre zu generieren

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

Um das Verfahren zu verwenden, rufen Sie es wie folgt auf:

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

Beachten Sie, dass ich eine globale temporäre Tabelle verwende. Das liegt daran, dass die Verwendung von EXEC zum Ausführen des dynamischen SQL-Befehls eine eigene Sitzung erstellt, sodass eine normale temporäre Tabelle nicht mehr für den nachfolgenden Code verwendet werden kann. Wenn eine globale temporäre Tabelle ein Problem darstellt, können Sie eine normale temporäre Tabelle verwenden, aber jedes nachfolgende SQL muss dynamisch sein, dh auch von der EXEC-Anweisung ausgeführt werden.


Wenn die Abfrage keinen Parameter enthält, verwenden OpenQuery OpenRowset . Verwenden OpenQuery andernfalls OpenRowset .

Grundlegend wäre, ein Schema nach einer gespeicherten Prozedur zu erstellen und in diese Tabelle einzufügen. z.B:

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

Wenn die gespeicherte Prozedur viele Spalten zurückgibt und Sie nicht möchten, dass eine temporäre Tabelle manuell erstellt wird, um das Ergebnis zu speichern, habe ich herausgefunden, dass der einfachste Weg darin besteht, in die gespeicherte Prozedur zu gehen und eine "in" -Klausel hinzuzufügen Letzte SELECT-Anweisung und fügen Sie der WHERE-Klausel 1 = 0 hinzu.

Führen Sie die gespeicherte Prozedur einmal aus und gehen Sie zurück und entfernen Sie den gerade hinzugefügten SQL-Code. Jetzt haben Sie eine leere Tabelle, die dem Ergebnis der gespeicherten Prozedur entspricht. Sie könnten entweder "Tabelle als Erstelle" für eine temporäre Tabelle erstellen oder einfach direkt in diese Tabelle einfügen.


Code

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

Ich hoffe das hilft. Bitte qualifizieren Sie sich entsprechend.


I met the same problem and here is what I did for this from Paul's suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

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 + ']')

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'


This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

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

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.


SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')




stored-procedures