geht dynamisches Pivot nicht mit #temp-Tabelle?

[openshinok]

hallo,
hat jemand eine Info für mich ob ein Pivot welches über dynamisches SQL ausgeführt wird nicht in eine temporären Tabelle (Bsp.: #temp01) schreiben kann?!

Ich musste mir jetzt über ##temp01 helfen...

Habe ich mir einen Fehler erlaubt oder geht es tatsächlich nicht?

in @strPIVOTString steht ca. folgendes: [1], [2], [3]


SET @strSQLPIVOT = 'SELECT Nr01 AS Zaehler, ' + @strPIVOTString;
SET @strSQLPIVOT = @strSQLPIVOT + 
'  INTO ##temp01 FROM (
	SELECT Nr01, Nr02, wert
	FROM #temp02
) AS Source
PIVOT (
	MAX(wert)
	FOR Nr02 IN ('+ @strPIVOTString +')
) AS PVT';

PRINT @strSQLPIVOT

-- Ausführen des dynamischen SQLs
EXEC(@strSQLPIVOT);

[hwoess]

Habe ich mir einen Fehler erlaubt oder geht es tatsächlich nicht?

Eine Fehlermeldung wäre interessanter gewesen.

Und wenn du aus #temp02 lesen kannst (oder wissen wir das nicht?) solltest du auch nach #temp01 schreiben können.
Das mit dem ## bezieht sich auf die Sichtbarkeit, solche Tabellen sind global sichtbar, während die mit nur einem # nur innerhalb der Session sichtbar sind, und das sollte trotz EXEC gegeben sein. So gesehen sollte das schon funktionieren. Bei geschachtelten Aufrufen wie hier muss natürlich der create-Befehl der Tabelle in der Reihenfolge schon vor dem Insert kommen. Aber davon gehe ich mal aus, vielleicht wurde ja nur ein O statt 0 verwendet.

bye,
Helmut

PS: hab's gerade selber ausprobiert mit diesem Beispiel:

create table #tmp (wert int)
declare @cmd varchar(99)
set @cmd = 'insert into #tmp values (4)'
exec (@cmd)
select * from #tmp
drop table #tmp

... geht

[openshinok]

Fehlermeldung:
Ungültiger Objektname '#tmpgaga'.



Hier mal mit Code zum selbst testen (mit Dank an O.Helper der den Code ursprünglich auf eine andere PIVOT-Frage von mir gepostet hat):

SET NOCOUNT ON
CREATE TABLE #Rechte
(script varchar(5), Mitarb varchar(5), Recht varchar(5));
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 1', 'Read');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 1', 'Write');
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 2', 'Write');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 2', 'Write');
GO


DECLARE @strSQL NVARCHAR(MAX)



SET @strSQL = 'SELECT script, [Mit 1], [Mit 2]
INTO #tmpgaga FROM (SELECT script, Mitarb, Recht
FROM #Rechte 
GROUP BY script, Mitarb, Recht
) AS Source
PIVOT (MAX(Recht)
FOR Mitarb IN ([Mit 1], [Mit 2])
) AS PVT
ORDER BY script';

EXEC(@strSQL)


SELECT *, 'austemp' AS Info FROM #tmpgaga


DROP TABLE #Rechte
DROP TABLE #tmpgaga

Dagegen geht:

SET NOCOUNT ON
CREATE TABLE #Rechte
(script varchar(5), Mitarb varchar(5), Recht varchar(5));
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 1', 'Read');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 1', 'Write');
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 2', 'Write');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 2', 'Write');
GO


DECLARE @strSQL NVARCHAR(MAX)



SET @strSQL = 'SELECT script, [Mit 1], [Mit 2]
INTO ##tmpgaga FROM (SELECT script, Mitarb, Recht
FROM #Rechte 
GROUP BY script, Mitarb, Recht
) AS Source
PIVOT (MAX(Recht)
FOR Mitarb IN ([Mit 1], [Mit 2])
) AS PVT
ORDER BY script';

EXEC(@strSQL)


SELECT *, 'austemp' AS Info FROM ##tmpgaga


DROP TABLE #Rechte
DROP TABLE ##tmpgaga

[hwoess]

Das Problem ist hier, dass ein SELECT ... INTO eine neue Tabelle erzeugt. Da dies im EXEC eine Stufe tiefer als die stored proc liegt bedeutet das, dass die Tabelle bei Ende des EXEC-Aufrufes auch gleich wieder zerstört wird - ist so per Definition einer Temp-Table mit nur einem #. Eine Temp-Table mit ## bleibt hingegen solange bestehen, solange irgendeine Session darauf zugreift. Die Lösung wäre, die Temp-Table schon in der stored proc zu erzeugen (wie schon in meiner ersten Antwort geschrieben) und im EXEC nur mehr ein normales INSERT zu verwenden, zB so:

SET NOCOUNT ON
CREATE TABLE #Rechte
(script varchar(5), Mitarb varchar(5), Recht varchar(5));
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 1', 'Read');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 1', 'Write');
INSERT INTO #Rechte VALUES ('Scr 1', 'Mit 2', 'Write');
INSERT INTO #Rechte VALUES ('Scr 2', 'Mit 2', 'Write');
GO


DECLARE @strSQL NVARCHAR(MAX)

select * into  #tmpgaga from #rechte where 1 = 2   -- erzeugt eine leere Tabelle

SET @strSQL = 'insert into #tmpgaga SELECT script, [Mit 1], [Mit 2]
FROM (SELECT script, Mitarb, Recht
FROM #Rechte 
GROUP BY script, Mitarb, Recht
) AS Source
PIVOT (MAX(Recht)
FOR Mitarb IN ([Mit 1], [Mit 2])
) AS PVT
ORDER BY script';

EXEC(@strSQL)

SELECT *, 'austemp' AS Info FROM #tmpgaga


DROP TABLE #Rechte
DROP TABLE #tmpgaga

bye,
Helmut

[openshinok]

hallo, danke für die Aufklärung! Hatte mir schon soetwas mit der Gültigkeit der temporärren Tabelle gedacht weil es ja mit ## funktioniert...

Das mit dem vorher erstellen funktioniert nicht so einfach weil natürlich meine Spaltenanzahl dynamisch ist und ich die Temporäre Tabelle zwingend? per dynamischen SQL erstellen muss.

Aber ich kann mir mir ## Tabellen helfen.

Als Info noch zu deinem

select * into  #tmpgaga from #rechte where 1 = 2   -- erzeugt eine leere Tabelle

Folgendes Statement erzeugt auch eine leere Tabelle, Top 0 gibt nur die Spalten zurück.

select TOP 0 * into  #tmpgaga from #rechte   -- erzeugt ebenfalls eine leere Tabelle

[hwoess]

Klar, in diesem Fall hilft nur eine Tabelle mit ## - endlich mal ein Fall, wo eine globale temporäre Tabelle Sinn macht. Allerdings wäre hier darauf aufzupassen, was passiert, wenn eine zweite Person zur selben Zeit ebenfalls diese stored proc ausführt, da so eine Tabelle ja für alle User sichtbar ist. Eigentlich müsste der zweite Aufrufer eine Fehlermeldung bekommen, dass diese globale Tabelle schon existiert ...

bye,
Helmut

PS: man könnte einen Wettbewerb machen: welche Möglichkeiten gibt es, eine leere Tabelle mit gleicher Struktur wie eine bestehende zu erzeugen

[ebis]

Allerdings wäre hier darauf aufzupassen, was passiert, wenn eine zweite Person zur selben Zeit ebenfalls diese stored proc ausführt, da so eine Tabelle ja für alle User sichtbar ist. Eigentlich müsste der zweite Aufrufer eine Fehlermeldung bekommen, dass diese globale Tabelle schon existiert ...

bye,
Helmut

Da die ganze Abfrage dynamisch erzeugt wird, könnte auch der Name der globalen Temp-Tabelle dynamisch (mit Username, SPID, Uhrzeit usw. (solange man unter 128 Zeichen bleibt) ) erzeugt werden...