Wstaw wyniki procedury składowanej do tabeli tymczasowej


1577

Jak mam to zrobić SELECT * INTO [temp table] FROM [stored procedure]? Nie FROM [Table]i bez definicji [temp table]?

Selectwszystkie dane z BusinessLinedo tmpBusLinedziała dobrze.

select *
into tmpBusLine
from BusinessLine

Próbuję tego samego, ale użycie stored procedurezwracającego dane nie jest takie samo.

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

Komunikat wyjściowy:

Msg 156, poziom 15, stan 1, wiersz 2 Niepoprawna składnia w pobliżu słowa kluczowego „exec”.

Przeczytałem kilka przykładów tworzenia tabeli tymczasowej o tej samej strukturze co wyjściowa procedura składowana, która działa dobrze, ale fajnie byłoby nie podawać żadnych kolumn.


22
Z SELECT * INTO [TABLE NAME] znasz kolumny, ponieważ są one kopiowane z oryginalnej tabeli. Właśnie tego chcę, gdybym zrobił to samo przeciwko procedurze przechowywanej.
Ferdeen,


7
Chcę tylko zaznaczyć, że „select * into tmpBusLine” tworzy stałą tabelę. Prawdopodobnie chcesz „select * do #tmpBusLine”. Jestem pewien, że oryginalny plakat już to odkrył, ale może pomóc innym, którzy znajdą ten post, ponieważ jest to obecnie najlepszy wynik wyszukiwania „wybierz do tabeli temp”
ktam33

2
Nie wiem, czy to rozwiązano, czy nie, ale przyczyną tego błędu jest słowo kluczowe from.
Wes Palmer,

9
Microsoft musi dodać SELECT * INTO FROM EXEC! Proszę!
kjmerf,

Odpowiedzi:


704

Możesz do tego użyć OPENROWSET . Spójrz. Dołączyłem również kod sp_configure, aby włączyć rozproszone zapytania Ad Hoc, na wypadek, gdyby nie zostało jeszcze włączone.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

28
To jest właściwy sposób, aby to zrobić. OPENROWSET to właściwie jedyny sposób traktowania wyników procedury składowanej jako wyrażenia tabelowego.
Rob Farley,

37
To trochę kłopotliwe po prostu wstawić do tabeli. Dużo konfiguracji do zrobienia. Również po wypróbowaniu dostałem komunikat „Msg 7357, poziom 16, stan 2, wiersz 1. Nie można przetworzyć obiektu„ EXEC GetPartyAnalysisData 146 ”. Dostawca OLE DB„ SQLNCLI ”dla połączonego serwera„ (null) ”wskazuje, że albo obiekt ma brak kolumn lub bieżący użytkownik nie ma uprawnień do tego obiektu. ” Musisz więc ustawić połączony serwer ...
Ferdeen,

10
Nie potrzebujesz połączonego serwera, ale musisz poprawnie ustawić parametry połączenia ... a także podać pełną ścieżkę do procedury składowanej, w tym nazwę bazy danych i właściciela sp.
MartW

18
eeeeew! odniesienie do tego samego serwera? paskudny. zdecydowanie bardziej hack niż konieczność ręcznego tworzenia tabeli tymczasowej
Tim Abell,

23
Zgadzam się, że jest to hack i prawdopodobnie należy go unikać, chyba że twoje plecy są oparte o ścianę. Zmiana sp na funkcję jest prawdopodobnie lepszym kątem do przyjęcia. MOIM ZDANIEM.
greg

623

Jeśli chcesz to zrobić bez uprzedniego zadeklarowania tabeli tymczasowej, możesz spróbować utworzyć funkcję zdefiniowaną przez użytkownika zamiast procedury składowanej i sprawić, by funkcja zdefiniowana przez użytkownika zwróciła tabelę. Alternatywnie, jeśli chcesz skorzystać z procedury składowanej, spróbuj czegoś takiego:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

170
Myślę, że chodziło o wygenerowanie schematu bez konieczności jawnego deklarowania go.
Craig

5
Chciałbym wiedzieć, jaka jest różnica między tym a powyższym rozwiązaniem @Aarona Altona. Ten wydaje się o wiele prostszy, ale nie jestem pewien, co do innych konsekwencji.
funkymushroom

11
To zadziała, ale jeśli kiedykolwiek dodasz dodatkowe kolumny do procedury przechowywanej SpGetRecords, to wybuchnie.
Brady Holt

15
Otrzymujesz tylko jeden INSERT INTO EXEC na stos wywołań. SpGetRecords i inne wywołane przez niego procy nie mogą używać tej strategii we własnym kodzie. Może to zaskoczyć opiekunów SpGetRecords.
Matt Stephenson,

33
To w ogóle nie odpowiada na pytanie i nie rozumiem, dlaczego jest tak pozytywnie oceniany? OP wyraźnie stwierdził „bez definiowania [tabeli temp]”, a twój pierwszy wiersz zawiera instrukcję tworzenia tabeli temp.
NickG

296

W SQL Server 2005 można użyć INSERT INTO ... EXECdo wstawienia wyniku procedury składowanej do tabeli. Z dokumentacji MSDNINSERT (w rzeczywistości dla SQL Server 2000):

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

122
Wymaga to wcześniejszego zdefiniowania autorów. Staram się tego uniknąć. Dzięki.
Ferdeen,

5
Tak myślałem. Tak przydatne wstawianie do tabel tmp w locie, ale nie tak przydatne, jeśli chcesz znać strukturę zestawu danych zwróconą z przechowywanego proc. Dzięki za pomoc
Ferdeen,


4
Aby użyć tego samego schematu, możesz wykonać kopię w następujący sposób: wybierz górną 0 * do tempTable z realTable ( stackoverflow.com/a/9206463/73794 )
Nawet Mien

@EvenMien Przez chwilę byłem podekscytowany, gdy zobaczyłem twój komentarz ... ale niestety to działa tylko wtedy, gdy wyniki twojego procesu faktycznie odzwierciedlają prawdziwy stół :(
BVernon

193

To jest odpowiedź na nieco zmodyfikowaną wersję twojego pytania. Jeśli możesz zrezygnować z procedury składowanej dla funkcji zdefiniowanej przez użytkownika, możesz użyć wbudowanej funkcji zdefiniowanej przez użytkownika o wartościach przechowywanych w tabeli. Zasadniczo jest to procedura składowana (pobiera parametry), która zwraca tabelę jako zestaw wyników; i dlatego będzie ładnie umieścić z oświadczeniem INTO.

Oto dobry szybki artykuł na ten temat i innych funkcji zdefiniowanych przez użytkownika. Jeśli nadal potrzebujesz sterowanej procedury składowanej, możesz owinąć wbudowaną funkcję tabelową zdefiniowaną przez użytkownika funkcją procedury składowanej. Procedura przechowywana po prostu przekazuje parametry, gdy wywołuje select * z wbudowanej funkcji zdefiniowanej przez użytkownika o wartościach przechowywanych w tabeli.

Na przykład masz wbudowaną funkcję zdefiniowaną przez użytkownika o wartości tabeli, aby uzyskać listę klientów dla określonego regionu:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Następnie możesz wywołać tę funkcję, aby uzyskać takie wyniki, jak:

SELECT * FROM CustomersbyRegion(1)

Lub zrobić WYBIERZ W:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Jeśli nadal potrzebujesz procedury składowanej, zawiń funkcję jako taką:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Myślę, że jest to najbardziej „bezhackowa” metoda uzyskiwania pożądanych rezultatów. Wykorzystuje istniejące funkcje, ponieważ były przeznaczone do użycia bez dodatkowych komplikacji. Poprzez zagnieżdżenie wbudowanej funkcji zdefiniowanej przez użytkownika w tabeli w procedurze przechowywanej, masz dostęp do tej funkcji na dwa sposoby. Plus! Masz tylko jeden punkt obsługi rzeczywistego kodu SQL.

Sugerowano użycie OPENROWSET, ale nie do tego służy funkcja OPENROWSET (From Books Online):

Obejmuje wszystkie informacje o połączeniu wymagane do uzyskania dostępu do danych zdalnych ze źródła danych OLE DB. Ta metoda jest alternatywą dla uzyskiwania dostępu do tabel na połączonym serwerze i jest jednorazową, ad hoc metodą łączenia i uzyskiwania dostępu do danych zdalnych przy użyciu OLE DB. Aby uzyskać częstsze odniesienia do źródeł danych OLE DB, użyj zamiast tego połączonych serwerów.

Użycie OPENROWSET wykona zadanie, ale spowoduje dodatkowe obciążenie związane z otwieraniem połączeń lokalnych i przesyłaniem danych. Może również nie być opcją we wszystkich przypadkach, ponieważ wymaga zezwolenia na zapytania ad hoc, które stanowi zagrożenie dla bezpieczeństwa i dlatego może nie być pożądane. Ponadto podejście OPENROWSET wyklucza stosowanie procedur przechowywanych zwracających więcej niż jeden zestaw wyników. Można to osiągnąć dzięki zawinięciu wielu funkcji zdefiniowanych przez użytkownika w wartość tabeli w jednej procedurze składowanej.


4
+1 Funkcja wyceniana w tabeli jest właściwym rozwiązaniem. Powinniśmy zwrócić uwagę na drobne wady: funkcja wyceniana w tabeli jest dodatkowym obiektem bazy danych i może być konieczne nadanie jej uprawnień.
spencer7593,

2
Uwielbiam to rozwiązanie. Jednym drobnym problemem, który trafiłem, jest to, że moja tabela nie może mieć kolejności według miejsca, w którym mogłaby to mieć w procedurze przechowywanej. Cóż, rozwiążę to
mrwaim

5
Jeszcze jeden problem - „Nie można uzyskać dostępu do tabel tymczasowych z poziomu funkcji”
mrwaim

7
Pierwotne pytanie brzmi: w jaki sposób tworzymy tabelę tymczasową z wynikami sp. To dobry wzór, ale nie rozwiązuje tego pytania
greg

16
greg, pierwszy wiersz w mojej odpowiedzi brzmi „To jest odpowiedź na nieco zmodyfikowaną wersję twojego pytania”. Twój komentarz jest zbędny.
Christian Loris

131
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')

2
Uzyskaj „Msg 208, poziom 16, stan 1, wiersz 1 Niepoprawna nazwa obiektu„ tmpBusLine ”(prawdopodobnie ponieważ nie jest zdefiniowana z góry).
Ferdeen

1
@Fds: przepraszam, początkowo nie zrozumiałem twojej prośby. Zaktualizowany o inne rozwiązanie.
Quassnoi

26
Świetne rozwiązanie. Jedno zastrzeżenie, musisz włączyć „DATA ACCESS” na swoim serwerze: EXEC sp_serveroption „TheServerName”, „DATA ACCESS”, TRUE
jcollum

8
Musisz także zezwolić na zdalny dostęp do serwera. Będzie to miało konsekwencje dla bezpieczeństwa.
BraveNewMath

7
To nie zadziała, jeśli docelowa procedura przechowywana korzysta z tabel tymczasowych
Sal

125

Najłatwiejsze rozwiązanie:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Jeśli nie znasz schematu, możesz wykonać następujące czynności. Należy pamiętać, że ta metoda wiąże się z poważnym zagrożeniem bezpieczeństwa.

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

jeśli nie znam kolumny zwróconego zestawu wyników, to ??? mam na myśli kolumna może się różnić. więc jak wstawić wynik do tabeli temp?
SHEKHAR SHETE

Możesz użyć OPENQUERY, ale nie jest to zalecane, ponieważ zawiera błędy bezpieczeństwa.
Tigerjz32,

1
„jeśli nie znam kolumny zwróconego zestawu wyników, to”, to nie możesz użyć jej w swojej logice. Jak wykorzystasz dane, jeśli nie wiesz, co to jest?
Adriaan Davel,

@AdriaanDavel Zgadzam się z tobą, że zawsze powinieneś znać swoje dane (najlepsza praktyka), ale może on powiedzieć, że są chwile, kiedy sproc zwraca dynamiczne kolumny i nie zawsze wiesz, jak będzie wyglądał schemat. W takim przypadku możesz użyć OPENROWSET, aby wstawić i utworzyć tabelę w locie. Są jednak oczywiste zagrożenia bezpieczeństwa ...
Tigerjz32,

1
@ nurettin czasami nie wiesz, co zwróci procedura składowana. Co się dzieje w takim przypadku? Jak można utworzyć tabelę tymczasową (gdy nie wiadomo, co zwróci procedura przechowywana) i wstawić do niej procedurę przechowywaną?
Tigerjz32

106

Gdy procedura przechowywana zwraca wiele kolumn, a Ty nie chcesz ręcznie „tworzyć” tabeli tymczasowej do przechowywania wyniku, znalazłem najłatwiejszy sposób, aby przejść do procedury przechowywanej i dodać klauzulę „do” na ostatnia instrukcja select i dodaj 1 = 0 do klauzuli where.

Uruchom procedurę składowaną raz i wróć i usuń właśnie dodany kod SQL. Teraz będziesz mieć pustą tabelę pasującą do wyniku procedury składowanej. Możesz albo „utworzyć skrypt tabeli jako tworzenie” dla tabeli tymczasowej, albo po prostu wstawić bezpośrednio do tej tabeli.


9
+1, doskonała sugestia. Możesz nawet dodać szybką zmienną opcjonalną do sproca o nazwie @TableCreate lub coś podobnego, gdy gdy nie jest null, wykonaj powyższe kroki. Nie wymaga zmiany sproca po skonfigurowaniu.
Ian Roke

1
@dotjoe Czy robisz SELECT INTOtabelę tymczasową i tworzysz tabelę skryptów podczas tworzenia z tabeli tymczasowej? Pojawiają się tabele tymczasowe, tempdbale nie mogę kliknąć prawym przyciskiem myszy i utworzyć skryptu. Każda pomoc jest mile widziana.
DotnetDude

2
@DotNetDude można select ... into new_tabledomyślnie utworzyć rzeczywistą tabelę.
dotjoe

Następnie pobierz szorstką definicję kolumny z pustego schematu tabeli; zamień „...” na końcu na legit TABLE_NAME:declare @s varchar(max)='';select @s=@s+','+COLUMN_NAME+' '+DATA_TYPE+isnull('('+case CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))end+')','')from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='...';select @s
user423430

To najlepsze rozwiązanie!
Lucas925

66
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;

3
nie odnosi się do pierwotnego pytania OP, wykonując wstawkę bez definiowania tabeli tymczasowej.
t.durden

48

Czy procedura przechowywana pobiera tylko dane lub je modyfikuje? Jeśli jest używany tylko do pobierania, możesz przekonwertować procedurę przechowywaną na funkcję i użyć wspólnych wyrażeń tabelowych (CTE) bez konieczności deklarowania jej w następujący sposób:

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

Jednak wszystko, co należy pobrać z CTE, powinno być użyte tylko w jednej instrukcji. Nie możesz zrobić a with temp as ...i spróbować użyć go po kilku wierszach SQL. Możesz mieć wiele CTE w jednej instrukcji dla bardziej złożonych zapytań.

Na przykład,

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)

1
To nie są tabele tymczasowe, to CTE. technet.microsoft.com/en-us/library/…
yucer

5
Dzięki @yucer ... Wydaje mi się, że nie wiedziałem, że nazywano je wtedy CTE :)
SO Użytkownik

48

Jeśli tabela wyników przechowywanego procesu jest zbyt skomplikowana, aby ręcznie wpisać instrukcję „utwórz tabelę” i nie możesz użyć OPENQUERY OR OPENROWSET, możesz użyć sp_help do wygenerowania listy kolumn i typów danych. Gdy masz już listę kolumn, wystarczy sformatować ją zgodnie z własnymi potrzebami.

Krok 1: Dodaj „do #temp” do zapytania wyjściowego (np. „Wybierz [...] do #temp z [...]”).

Najprostszym sposobem jest bezpośrednia edycja zapytania wyjściowego w proc. jeśli nie możesz zmienić zapisanego proc, możesz skopiować zawartość do nowego okna zapytania i tam zmodyfikować zapytanie.

Krok 2: Uruchom sp_help w tabeli tymczasowej. (np. „exec tempdb..sp_help #temp”)

Po utworzeniu tabeli tymczasowej uruchom sp_help w tabeli tymczasowej, aby uzyskać listę kolumn i typów danych, w tym wielkość pól varchar.

Krok 3: Skopiuj kolumny danych i typy do instrukcji tworzenia tabeli

Mam arkusz programu Excel, którego używam do sformatowania danych wyjściowych sp_help w instrukcji „create table”. Nie potrzebujesz nic szczególnego, wystarczy skopiować i wkleić do edytora SQL. Użyj nazw, rozmiarów i typów kolumn, aby skonstruować instrukcję „Utwórz tabelę x [...]” lub „deklaruj @ x tabelę [...]”, której można użyć do wstawienia wyników procedury składowanej.

Krok 4: Wstaw do nowo utworzonej tabeli

Teraz będziesz mieć zapytanie podobne do innych rozwiązań opisanych w tym wątku.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

Tej techniki można również użyć do konwersji tabeli tymczasowej ( #temp) na zmienną tabelową ( @temp). Chociaż może to być więcej niż tylko create tablesamodzielne napisanie instrukcji, zapobiega ręcznym błędom, takim jak literówki i niedopasowania typów danych w dużych procesach. Debugowanie literówki może zająć więcej czasu niż napisanie zapytania.


37

Jeśli OPENROWSET powoduje problemy, istnieje inny sposób od 2012 roku; skorzystać z sys.dm_exec_describe_first_result_set_for_object, jak wspomniano tutaj: odzyskać nazwy kolumn i typy procedury przechowywanej?

Najpierw utwórz tę procedurę składowaną, aby wygenerować SQL dla tabeli tymczasowej:

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

Aby skorzystać z tej procedury, wywołaj ją w następujący sposób:

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

Zauważ, że używam globalnej tabeli tymczasowej. Jest tak, ponieważ użycie EXEC do uruchomienia dynamicznego SQL tworzy własną sesję, więc zwykła tabela tymczasowa byłaby poza zakresem dla jakiegokolwiek kolejnego kodu. Jeśli globalne tabeli tymczasowej jest problem, to można użyć zwykłej tabeli tymczasowej, ale każda następna SQL musiałyby być dynamiczny, to znaczy wykonywane również przez instrukcję EXEC.


4
Zapomniałeś utworzyć tabeli z @SQL.
Trisped

32

Quassnoi umieścił mnie tam przez większość czasu, ale brakowało jednej rzeczy:

**** Musiałem użyć parametrów w procedurze przechowywanej. ****

I OPENQUERY nie pozwala na to:

Znalazłem więc sposób na pracę z systemem, a także nie musiałem usztywniać definicji tabeli i redefiniować ją w innej procedurze przechowywanej (i oczywiście ryzykować, że się zepsuje)!

Tak, można dynamicznie utworzyć definicję tabeli zwróconą z procedury składowanej za pomocą instrukcji OPENQUERY z fałszywymi zmiennymi (o ile zestaw NO RESULT zwraca taką samą liczbę pól i znajduje się w tej samej pozycji co zestaw danych z dobrymi danymi).

Po utworzeniu tabeli możesz używać procedury przechowywanej exec do tabeli tymczasowej przez cały dzień.


Aby pamiętać (jak wskazano powyżej), musisz włączyć dostęp do danych,

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

Kod:

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

Dzięki za informacje, które zostały dostarczone pierwotnie ... Tak, w końcu nie muszę tworzyć tych wszystkich fałszywych (ścisłych) definicji tabeli podczas korzystania z danych z innej procedury składowanej lub bazy danych i tak, możesz również użyć parametrów.

Wyszukaj tagi referencyjne:

  • Procedura przechowywana SQL 2005 do tabeli temp

  • openquery z procedurą składowaną i zmiennymi 2005

  • openquery ze zmiennymi

  • wykonać procedurę przechowywaną w tabeli temp

Aktualizacja: nie będzie działać z tabelami tymczasowymi więc musiałem skorzystać z ręcznego tworzenia tabeli tymczasowej.

Uwaga Bummer : nie będzie działać z tymczasowymi stolikami , http://www.sommarskog.se/share_data.html#OPENQUERY

Odniesienie: Następną rzeczą jest zdefiniowanie LOCALSERVER. W tym przykładzie może wyglądać jak słowo kluczowe, ale w rzeczywistości jest to tylko nazwa. Tak to się robi:

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

Aby utworzyć serwer połączony, musisz mieć uprawnienie ZMIENIĆ DOWOLNY SERWER lub być członkiem dowolnej ze stałych ról serwera sysadmin lub setupadmin.

OPENQUERY otwiera nowe połączenie z SQL Server. Ma to pewne implikacje:

Procedura wywoływana za pomocą OPENQUERY nie może odwoływać się do tabel tymczasowych utworzonych w bieżącym połączeniu.

Nowe połączenie ma własną domyślną bazę danych (zdefiniowaną przez sp_addlinkedserver, domyślnie jest to master), więc cała specyfikacja obiektu musi zawierać nazwę bazy danych.

Jeśli masz otwartą transakcję i trzymasz blokady podczas wywoływania OPENQUERY, wywoływana procedura nie może uzyskać dostępu do tego, co blokujesz. Oznacza to, że jeśli nie będziesz ostrożny, sam się zablokujesz.

Łączenie nie jest bezpłatne, więc występuje obniżenie wydajności.


1
Jeśli nie znasz nazwy swojego serwera, użyj SELECT @@SERVERNAME. Możesz także użyćEXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
Contango

24

Jeśli masz szczęście, że masz SQL 2012 lub nowszy, możesz użyć dm_exec_describe_first_result_set_for_object

Właśnie edytowałem sql dostarczone przez gotqn. Dzięki, gotqn.

Spowoduje to utworzenie globalnej tabeli temp o nazwie takiej samej jak nazwa procedury. Tabela temperatur może być później używana zgodnie z wymaganiami. Tylko nie zapomnij upuścić go przed ponownym uruchomieniem.

    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
Świetny! Tylko jedna uwaga: użyj sys.all_objectszamiast, sys.proceduresjeśli chcesz to zrobić dla wbudowanych procedur przechowywanych.
Gert Arnold,

2
Nie powiedzie się to również, jeśli SP użyje w nim tabel tymczasowych. (ale bardzo przydatne jest, aby mieć to jako proc w swoim arsenale)
Trubs

23

Ten zapisany proces wykonuje zadanie:

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

Jest to drobna przeróbka tego: wstaw wyniki procedury przechowywanej do tabeli , aby faktycznie działała.

Jeśli chcesz, aby działał ze stołem tymczasowym, musisz użyć ##GLOBALstołu i upuścić go później.


17

Aby wstawić pierwszy zestaw rekordów procedury składowanej do tabeli tymczasowej, musisz wiedzieć:

  1. tylko pierwszy zestaw wierszy procedury składowanej można wstawić do tabeli tymczasowej
  2. procedura składowana nie może wykonywać dynamicznej instrukcji T-SQL ( sp_executesql)
  3. najpierw musisz zdefiniować strukturę tabeli tymczasowej

Powyższe może wyglądać na ograniczenie, ale IMHO ma sens - jeśli używasz sp_executesql, możesz raz zwrócić dwie kolumny i raz dziesięć, a jeśli masz wiele zestawów wyników, nie możesz również wstawić ich do kilku tabel - możesz wstawić maksimum w dwóch tabelach w jednej instrukcji T-SQL (przy użyciu OUTPUTklauzuli i bez wyzwalaczy).

Problem polega głównie na zdefiniowaniu tymczasowej struktury tabeli przed wykonaniem EXEC ... INTO ...instrukcji.

Pierwszy działa z, OBJECT_IDpodczas gdy drugi i trzeci działa również z zapytaniami Ad-hoc. Wolę używać DMV zamiast sp, ponieważ możesz używać CROSS APPLYi budować tymczasowe definicje tabel dla wielu procedur jednocześnie.

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;

Zwróć także uwagę na system_type_namepole, ponieważ może być bardzo przydatne. Przechowuje pełną definicję kolumny. Na przykład:

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

i możesz użyć go bezpośrednio w większości przypadków, aby utworzyć definicję tabeli.

Tak więc myślę, że w większości przypadków (jeśli procedura przechowywana spełnia określone kryteria) możesz łatwo zbudować dynamiczne instrukcje rozwiązywania takich problemów (utwórz tabelę tymczasową, wstaw wynik w niej przechowywany, rób to, czego potrzebujesz z danymi) .


Należy zauważyć, że powyższe obiekty nie definiują danych pierwszego zestawu wyników w niektórych przypadkach, takich jak wykonanie dynamicznych instrukcji T-SQL lub użycie tymczasowych tabel w procedurze przechowywanej.


praktyczna obserwacja ograniczeń: jeśli musisz wstawić dane wyjściowe jakiegoś sp (pozwala to nazwać SP_LEVEL_0), aby tabela temp dynamicznie utworzona przy użyciu powyższego podejścia w innym sp (pozwala to nazwać SP_LEVEL_1), nie możesz zrobić tej samej sztuczki dla wyjścia tego SP_LEVEL_1, aby kolejna tabela temp. w SP_LEVEL_2
nahab

17
  1. Tworzę tabelę z następującym schematem i danymi.
  2. Utwórz procedurę składowaną.
  3. Teraz wiem, jaki jest wynik mojej procedury, dlatego wykonuję następujące zapytanie.

    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

    WARTOŚCI (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] Wł.

    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;

15

Jeśli zapytanie nie zawiera parametru, użyj OpenQueryinnego użycia OpenRowset.

Podstawową rzeczą byłoby utworzenie schematu zgodnie z procedurą przechowywaną i wstawienie do tej tabeli. na przykład:

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

13

Kod

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

Mam nadzieję, że to pomoże. Zakwalifikuj się odpowiednio.


11

Znalazłem przekazywanie tablic / tabel danych do przechowywanych procedur, które mogą dać ci inny pomysł na rozwiązanie problemu.

Łącze sugeruje użycie parametru typu obrazu do przejścia do procedury składowanej. Następnie w procedurze przechowywanej obraz jest przekształcany w zmienną tabelową zawierającą oryginalne dane.

Być może istnieje sposób, w jaki można tego użyć z tymczasowym stołem.


4
Nie jest to już wymagane w wersjach Sql2008 i późniejszych z wprowadzeniem parametrów wartości tabeli . Teraz możesz bezpośrednio przekazać zestaw danych .net lub obiekt danych do procedury składowanej SQL z koniecznością konwersji na bajty, jak wspomniano w powyższym linku
EndlessSpace

10

Spotkałem ten sam problem i oto, co z tym zrobiłem sugestii Paula . Najważniejsze jest tutaj, NEWID()aby uniknąć jednoczesnego uruchamiania procedur / skryptów przez wielu użytkowników, co jest problemem dla globalnej tabeli tymczasowej.

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

9

Inną metodą jest utworzenie typu i użycie PIPELINED, aby następnie przekazać obiekt. Ogranicza się to jednak do znajomości kolumn. Ma jednak tę zaletę, że potrafi:

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

Co to jest? Wydaje się, że nie ma to nic wspólnego z SQL Server, o który chodzi w tym pytaniu
Martin Smith,

8

Jest to prosty 2-etapowy proces: - utwórz tymczasowy stół - Wstaw do tymczasowego stołu.

Kod do wykonania tego samego:

CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable 
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;

Doceniony; bardzo podobne do istniejących odpowiedzi.
iokevins

6

Po przeszukaniu znalazłem sposób, aby dynamicznie utworzyć tabelę temp dla dowolnej procedury składowanej bez użycia OPENROWSETlub OPENQUERYużywania ogólnego schematu definicji wyniku procedury składowanej, zwłaszcza gdy nie jesteś administratorem bazy danych.

Serwer Sql ma wbudowany proc, sp_describe_first_result_setktóry może dostarczyć schemat dowolnego zestawu wyników procedur. Utworzyłem tabelę schematów na podstawie wyników tej procedury i ręcznie ustawiłem wszystkie pola na NULLABLE.

declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters 
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)

-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden  bit  NULL, column_ordinal   int  NULL, name sysname NULL, is_nullable   bit  NULL, system_type_id   int  NULL, system_type_name nvarchar(256) NULL,
max_length  smallint  NULL, precision   tinyint  NULL,  scale   tinyint  NULL,  collation_name  sysname NULL, user_type_id  int NULL, user_type_database    sysname NULL,
user_type_schema    sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name   nvarchar(4000),xml_collection_id    int NULL,xml_collection_database    sysname NULL,
xml_collection_schema   sysname NULL,xml_collection_name    sysname NULL,is_xml_document    bit  NULL,is_case_sensitive bit  NULL,is_fixed_length_clr_type  bit  NULL,
source_server   sysname NULL,source_database    sysname NULL,source_schema  sysname NULL,source_table   sysname NULL,source_column  sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key   bit NULL,is_updateable  bit NULL,is_computed_column bit NULL,is_sparse_column_set   bit NULL,ordinal_in_order_by_list   smallint NULL,
order_by_list_length    smallint NULL,order_by_is_descending    smallint NULL,tds_type_id   int  NULL,tds_length    int  NULL,tds_collation_id  int NULL,
tds_collation_sort_id   tinyint NULL)


-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0

-- Create a query to generate and populate a global temp table from above results
select 
@qry = 'Create table ##t(' +
stuff(  
    (select ',' + name + ' '+ system_type_name + ' NULL'
    from #d d For XML Path, TYPE)
    .value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')

insert into ##t 
Exec '+@procname+' ' + @param

Exec sp_executesql @qry

-- Use below global temp table to query the data as you may
select * from ##t

-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d 

Opracowany i przetestowany na wersji Sql Server - Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (kompilacja 17134 :)

Możesz dostosować schemat używanej wersji serwera SQL (jeśli to konieczne).


4

Jeśli znasz parametry, które są przekazywane, i jeśli nie masz dostępu do sp_configure, edytuj procedurę przechowywaną z tymi parametrami i to samo można zapisać w tabeli globalnej ##.


3

Można to zrobić w programie SQL Server 2014+, pod warunkiem że procedura składowana zwraca tylko jedną tabelę. Jeśli ktoś znajdzie sposób na zrobienie tego dla wielu tabel, chciałbym o tym wiedzieć.

DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = '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(@storedProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName

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

SELECT * FROM myTableName

DROP TABLE myTableName

Spowoduje to pobranie definicji zwróconej tabeli z tabel systemowych i wykorzystanie jej do utworzenia tabeli tymczasowej. Następnie można wypełnić go z procedury przechowywanej, jak podano wcześniej.

Istnieją również warianty tego, które działają również z dynamicznym SQL.


2

Kilka lat spóźniłem się na to pytanie, ale potrzebowałem czegoś takiego do szybkiego i brudnego generowania kodu. Sądzę, że jak inni stwierdzili, po prostu łatwiej jest zdefiniować tabelę tymczasową z góry, ale ta metoda powinna działać w przypadku prostych zapytań procedur przechowywanych lub instrukcji SQL.

To będzie trochę skomplikowane, ale pożycza od współtwórców tutaj, jak również rozwiązanie Paula White'a z DBA Stack Exchange Uzyskaj kolumny wyników typu procedura przechowywana . Ponownie, aby powtórzyć to podejście i przykład nie jest przeznaczony dla procesów w środowisku wielu użytkowników. W tym przypadku definicja tabeli jest ustawiana na krótki czas w globalnej tabeli tymczasowej w celu odniesienia przez proces szablonu generowania kodu.

Nie przetestowałem tego w pełni, więc mogą istnieć zastrzeżenia, więc możesz przejść do linku MSDN w odpowiedzi Paula White'a. Dotyczy to SQL 2012 i nowszych wersji.

Najpierw użyj procedury składowanej sp_describe_first_result_set, która przypomina opis Oracle.

Spowoduje to ocenę pierwszego wiersza pierwszego zestawu wyników, więc jeśli procedura składowana lub instrukcja zwróci wiele zapytań, będzie opisywać tylko pierwszy wynik.

Utworzyłem przechowywany proc, aby rozbić zadania, które zwracają pojedyncze pole do wyboru, aby utworzyć definicję tabeli temp.

CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
     @sql NVARCHAR(4000)
    ,@table_name VARCHAR(100)
    ,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TempTableDefinition NVARCHAR(MAX)
    DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)

    DECLARE @ResultDefinition TABLE (  --The View Definition per MSDN
      is_hidden         bit NOT NULL
    , column_ordinal    int NOT NULL
    , [name]            sysname NULL
    , is_nullable       bit NOT NULL
    , system_type_id    int NOT NULL
    , system_type_name  nvarchar(256) NULL
    , max_length        smallint NOT NULL
    , [precision]       tinyint NOT NULL
    , scale             tinyint NOT NULL
    , collation_name    sysname NULL    
    , user_type_id      int NULL
    , user_type_database    sysname NULL    
    , user_type_schema  sysname NULL
    , user_type_name    sysname NULL    
    , assembly_qualified_type_name      nvarchar(4000)  
    , xml_collection_id         int NULL
    , xml_collection_database   sysname NULL    
    , xml_collection_schema     sysname NULL    
    , xml_collection_name       sysname NULL
    , is_xml_document           bit NOT NULL            
    , is_case_sensitive         bit NOT NULL            
    , is_fixed_length_clr_type  bit NOT NULL    
    , source_server             sysname NULL            
    , source_database           sysname NULL
    , source_schema             sysname NULL
    , source_table              sysname NULL
    , source_column             sysname NULL
    , is_identity_column        bit NULL
    , is_part_of_unique_key     bit NULL
    , is_updateable             bit NULL
    , is_computed_column        bit NULL
    , is_sparse_column_set      bit NULL
    , ordinal_in_order_by_list  smallint NULL   
    , order_by_is_descending    smallint NULL   
    , order_by_list_length      smallint NULL
    , tds_type_id               int NOT NULL
    , tds_length                int NOT NULL
    , tds_collation_id          int NULL
    , tds_collation_sort_id     tinyint NULL
    )

    --Insert the description into table variable    
    INSERT @ResultDefinition
    EXEC sp_describe_first_result_set @sql

    --Now Build the string to create the table via union select statement
    ;WITH STMT AS (
        SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
        UNION ALL

        SELECT 
         CONCAT(
                CASE column_ordinal
                    WHEN 1 THEN '     ' ELSE '   , ' END  --Determines if comma should precede
                , QUOTENAME([name]) , '   ', system_type_name  -- Column Name and SQL TYPE
                ,CASE is_nullable 
                    WHEN 0 THEN '   NOT NULL' ELSE '   NULL' END --NULLABLE CONSTRAINT          
               ) AS TextVal
        FROM @ResultDefinition WHERE is_hidden = 0  -- May not be needed
        UNION ALL

        SELECT N');' + @NewLine
    ) 

    --Now Combine the rows to a single String
    SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT

    SELECT @TableDefinition = @TempTableDefinition
END

Zagadką jest to, że musisz użyć tabeli globalnej, ale musisz uczynić ją wystarczająco wyjątkową, abyś mógł z niej często tworzyć i tworzyć bez obaw o kolizję.
W przykładzie użyłem Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) dla zmiennej globalnej, zastępując łączniki znakiem podkreślenia

DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql can be a stored procedure name like dbo.foo without parameters

DECLARE @TableDef NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet] 
    @sql, @GlobalTempTable, @TableDef OUTPUT

--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef 

--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
    EXEC sp_executesql @sql 

--Select the results into your undefined Temp Table from the Global Table
SELECT * 
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT * FROM #MyTempTable

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

Ponownie przetestowałem go tylko z prostymi zapytaniami z procedury składowanej i prostymi zapytaniami, więc twój przebieg może się różnić. Mam nadzieję, że to komuś pomoże.


1

Cóż, musisz utworzyć tabelę tymczasową, ale nie musi ona mieć odpowiedniego schematu ... Utworzyłem procedurę składowaną, która modyfikuje istniejącą tabelę tymczasową, tak aby zawierała wymagane kolumny z odpowiednimi danymi wpisz i uporządkuj (usuwając wszystkie istniejące kolumny, dodając nowe kolumny):

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Uwaga: to nie zadziała, jeśli sys.dm_exec_describe_first_result_set_set_object nie może określić wyników procedury składowanej (na przykład, jeśli używa tabeli tymczasowej).


0

Jeśli zezwolisz dynamicznemu SQL na utworzenie tabeli tymczasowej, ta tabela jest własnością połączenia dynamicznego SQL, w przeciwieństwie do połączenia, z którego wywoływana jest procedura składowana.

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;

wprowadź opis zdjęcia tutaj

DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

SELECT * FROM #Pivoted;

Msg 208, poziom 16, stan 0 Niepoprawna nazwa obiektu „#Pivoted”. Wynika to z faktu, że #Pivoted jest własnością połączenia Dynamic SQL. Więc ostatnia instrukcja

SELECT * FROM #Pivoted

zawodzi.

Jednym ze sposobów na rozwiązanie tego problemu jest upewnienie się, że wszystkie odwołania do #Pivoted pochodzą z samego zapytania dynamicznego:

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;


DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

wprowadź opis zdjęcia tutaj


-5

Zrobiłbym następujące

  1. Utwórz (przekonwertuj SP na) UDF (wartość tabeli UDF).

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


2
Twój pierwszy krok może być przeszkodą. Na przykład, jeśli oryginalny SP używa tabel tymczasowych. UDF nie mogą używać tabel tymczasowych.
yumper
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.