Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

CTE in temp Table speichern

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Ruediger007, 28 Mai 2013.

  1. Ruediger007

    Ruediger007 Benutzer

    Hallo!

    Ich habe mir folgende CTE zusammengebaut in der jedes Datum von X bis heute aufgelistet wird.
    Code:
    DECLARE @startDate DATETIME
    DECLARE @endDate DATETIME
     
    SET @startDate = '2013-01-01'
    SET @endDate = GETDATE()
     
     
    ;WITH CTE_Dates AS
    (
        SELECT @startDate AS DT
        UNION ALL
        SELECT DATEADD(DD,1,DT) FROM CTE_Dates
        WHERE DT <= @endDate
    )
    SELECT * FROM CTE_Dates
    OPTION (MAXRECURSION 0)
    Jetzt muss ich aber mit der Tabelle in verschiedener Art und Weise in einer Stored Procedure weiterarbeiten und müsste sie dafür in eine temporäre Tabelle kopieren. Ich weiss nur nicht wie. Mein Ansatz war:

    Code:
    CREATE TABLE #DATUMS_TABELLE(Datum as DATETIME)
    DECLARE @endDate DATETIME
     
    SET @startDate = '2013-01-01'
    SET @endDate = GETDATE()
    INSERT INTO #DATUMS_TABELLE
    ;WITH CTE_Dates AS
    (
        SELECT @startDate AS DT
        UNION ALL
        SELECT DATEADD(DD,1,DT) FROM CTE_Dates
        WHERE DT <= @endDate
    )
    SELECT * FROM CTE_Dates
    OPTION (MAXRECURSION 0)
    Aber das funktioniert nicht. Bei dem Aufruf
    Code:
     SELECT * FROM #DATUMS_TABELLE
    bekomme ich die Fehlermeldung Incorrect syntax near the keyword 'FROM.
    Kann mir hier vielleicht jemand weiterhelfen?
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Geht nicht einfach ein create table as ... oder ein insert into ... select ...?

    Das geht in PG:

    Code:
    test=# create table my_tmp_table as select '2013-01-01'::date + s * '1day'::interval as datum from generate_series(0,5) s;
    SELECT 6
    test=*# select * from my_tmp_table ;
            datum
    ---------------------
     2013-01-01 00:00:00
     2013-01-02 00:00:00
     2013-01-03 00:00:00
     2013-01-04 00:00:00
     2013-01-05 00:00:00
     2013-01-06 00:00:00
    (6 rows)
    
    und

    Code:
    test=*# create table my_tmp_table (d date);
    CREATE TABLE
    test=*# insert into my_tmp_table select  '2013-01-01'::date + s * '1day'::interval from generate_series(0,5) s;
    INSERT 0 6
    test=*# select * from my_tmp_table ;
         d
    ------------
     2013-01-01
     2013-01-02
     2013-01-03
     2013-01-04
     2013-01-05
     2013-01-06
    (6 rows)
    
    Sollte doch auch in M$SQL so gehen, oder?

    Andreas
     
  3. Ruediger007

    Ruediger007 Benutzer

    Hallo Andreas,

    ich habe versucht den Code durchlaufen zu lassen, aber da bekomme ich zig Fehler. Leider kenne ich mich gerade mal ein wenig mit MSSQL aus, so dass ich nicht in der Lage bin, den Code anzupassen.

    Aber danke trotzdem!

    EDIT:
    Ok, die Lösung war:
    Mache aus
    Code:
    SELECT * FROM CTE_Dates
    die Zeile
    Code:
    SELECT * INTO #DATUMS_TABELLE FROM CTE_Dates
     
  4. ukulele

    ukulele Datenbank-Guru

    Was für einen Zweck verfolgst du eigentlich und warum willst du die Tabelle mit allen Tagen jedesmal neu erstellen? Nur so aus Neugierde
     
  5. Ruediger007

    Ruediger007 Benutzer

    Hallo Ukulele!

    Ich führe einfach mal aus, was ich mache. Es wäre natürlich super, wenn du mir dazu einen Kommentar hinterlassen würdest. Ich bin nämlich sehr neu in SQL und Datenbanken und habe mir das alles selbst zusammengereimt.

    Ich arbeite an einem time-recording programm mit vb.net als benutzerschnittstelle und sql-server.

    Meine Zieltabelle soll so aussehen:
    Datum, Monatsname, Datum_Name (also Freitag, Samstag usw.), Kalenderwoche, Mitarbeiter, Soll-Arbeitsstunden, Ist-Arbeitsstunden.

    Daraus generiere ich dann für jeden Mitarbeiter (läuft über stored procedure mit windows authentication, also jeder Mitarbeiter kann nur seine Sachen abfragen):
    - Monatsübersichten (Im Frontet gibt es Tabs für jeden Monat mit allen Einträgen für den Monat)
    - Eine Übersicht je Kalenderwoche (Soll, Ist und Überstunden pro Woche summiert)
    - Eine Übersicht je Monat (Soll, Ist und Überstunden pro Monat summiert)

    Da die Mitarbeiter auch am Wochenende arbeiten können und es natürlich Feiertage gibt (Feiertagstabelle ist angelegt) arbeite ich auf Tagesbasis. Ein weiterer Grund hierfür ist, dass manche Mitarbeiter nur Teilzeit arbeiten (Mo 4 Std, Di nicht, Mi 7 Std) es aber auch möglich ist, dass sie an anderen Tagen arbeiten .

    Aus diesem Grund habe ich mir überlegt die o.g. Tabelle zu erstellen und daraus die jeweiligen Subtabellen abzurufen.

    Und bei diesem Prozess brauche ich dann (glaube ich) die Tabelle.

    VG

    Ruediger
     
  6. Tommi

    Tommi Datenbank-Guru

    Hi,

    die Frage von ukulele ist nicht ganz unberechtigt. Es ist natürlich wesentlich performanter, wenn man die Tage und davon abhängigen Werte schon in einer Tabelle vorhält.
    Dennoch schrecken davor viele zurück, da natürlich auch für die zukünftigen Jahre die Daten vorgehalten werden müssen. Oft kann man nicht abschätzen, ob denn 10 oder 20 Jahre dafür angesetzt werden müssen/sollen.

    Generell gibt es aber natürlich auf dem SQL-Server die Möglichkeit eine solche Tabelle über eine Tabellenwertfunktion so bereitzustellen, dass diese dynamisch den gewünschten Zeitraum abdeckt.
    Ich habe hierzu mal eine Funktion geschrieben die wie folgt aussieht:

    Code:
    CREATE FUNCTION dbo.fct_Tage_Zeitraum(@vonDatum datetime, @bisDatum datetime) RETURNS @RET TABLE
     ( Datum datetime,
      Jahr as YEAR(Datum),
      QuartalNr as DATEPART(quarter, Datum),
      Quartal as 'Quartal '+CAST(DATEPART(quarter, Datum) as varchar(10)),
      MonatNr as MONTH(Datum),
      Monat as DATENAME(month, Datum),
      KW as DATEPART(ISO_WEEK, Datum),
      Wochentag as DATENAME(weekday, Datum)
     )
    AS
    BEGIN
     WHILE @vonDatum<[EMAIL]=@bisDatum[/EMAIL]
     BEGIN
      INSERT INTO @RET (Datum) VALUES (@vonDatum)
      SET @vonDatum=DATEADD(day, 1, @vonDatum)
     END
     RETURN
    END
    Die Abfrage dieser Funktion erfolgt dann über diese Syntax:

    Code:
    SELECT *
    FROM dbo.fct_Tage_Zeitraum('01.01.2009', '31.12.2013')
    Diese Ermittlung ist aber wesentlich unperformanter, da auf eine Tabellenwertfunktion kein Index gesetzt werden kann, wie das bei einer fixen Tabelle der Fall wäre.
    In einer festen Tabelle sind die Daten auch jederzeit verfügbar und müssen nicht erst über eine Schleife (ob jetzt wie bei mir mit WHILE oder über die WITH-Funktion) erstellt werden,



    Viele Grüße,
    Tommi
     
    Ruediger007 und ukulele gefällt das.
  7. ukulele

    ukulele Datenbank-Guru

    Ich glaube die Lösung von Tommi gabs hier erst vor ein paar Tagen und diente auch zur Darstellung von Auswertungen in einem ähnlichen Fall.

    Ich würde glaube ich auch mit einer (fixen) Tabelle arbeiten in der einfach alle Tage stehen allein der einfacheren Handhabung wegen. Wenn du allerdings in deinen Auswertungen immer nur Tage oder Wochen aufführst, in denen auch Zeiten aufgelaufen sind, würde ich nur mit GROUP BY etc. arbeiten, dann bräuchtest du keine derartige Tabelle.
     
    Ruediger007 gefällt das.
  8. Ruediger007

    Ruediger007 Benutzer

    Hallo!
    Ich werden mir jetzt mal Gedanken machen, ob ich das mit Group by etc. hinbekommen und ansonsten die fixe Tabelle nutzen.
    Herzlichen Danke für die Tips!
    Viele Grüße
    Ruediger
     
  9. Ruediger007

    Ruediger007 Benutzer

    Hallo! ich habe nochmal eineFrage, die mit der Tabelle zutun hat, die wir oben hergeleitet haben.

    Ich habe die jetzt als fixe Tabelle hinzugefuegt.

    Felder:
    TAB_ID, Mitarbeiter_ID, DATUM, KW, MONAT, WOCHENTAG usw.
    1,1,01.01.2013,1,Januar,Dienstag
    2,1,01.02.2013.1,Januar,Mittwoch
    ...
    Für jeden Mitarbeiter hat also für jeden Tag in einem Jahr einen Eintrag.

    Die Tabelle ist mit der Mitarbeiter Tabelle verbunden über die Mitarbeiter_ID. Diese Tabelle ist mit der Verträge Tabelle über die Mitarbeiter_ID verbunden und diese wiederum über die Zeiten Tabelle über die Vertrags_ID. In dieser ZeitenTabelle habe ich die Felder:

    TAB2_ID, Vertrags_ID, Arbeitszeiten in Stunden, Tag
    1, 1, 8, Montag
    2,1,8,Dienstag
    3,1,8,Mittwoch
    4,1,8,Donnerstag
    5,1,8,Freitag
    6,2,4,Monatag
    7,2,Null,Dienstag
    8,2,6,Mittwoch
    9,2,NULL,Donnerstag
    10,2,5,Freitag

    Das Ziel ist jetzt eigentlich, dass ich einen Join machen in dem die Felder aus Tabelle 1 drin stehen und pro Tag die Informationen über die zu arbeitenden Stunden mit drinstehen. also

    TAB_ID, Mitarbeiter_ID, DATUM, KW, MONAT, WOCHENTAG usw.
    1,1,01.01.2013,1,Januar,Dienstag,8
    2,1,01.02.2013.1,Januar,Mittwoch,8

    Ich denke, dass muss irgendwie durchgeloopt werden für jeden Mitarbeiter, hänge aber gerade. Damit ihr nicht denkt, ich möchte andere die Arbeit machen lassen, poste ich was ich bis jetzt habe, aber ich glaube, das ist irgendwie ein falscher Ansatz.

    Code:
     
    SET @LETZTER_MA =  (SELECT MAX(MA_ID)  FROM Mitarbeiter)
    PRINT @LETZTER_MA
    SET @i = 1
     
    WHILE @i <= @LETZTER_MA
    BEGIN
        Select    TU.Datum, TU.Jahr, TU.Kalenderwoche, TU.MA_ID,
                TU.Monat, TU.MonatNr, TU.Wochentag,
                (xxx) as Arbeitsstunden
               
        from dbo.Tagesuebersicht as TU
        LEFT JOIN dbo.Mitarbeiter Mit
        on TU.MA_ID = Mit.MA_ID
        LEFT JOIN dbo.Vertrag Vert
        on Mit.MA_ID = Vert.MA_ID
        LEFT JOIN dbo. ZeitenTabelle Arb
        on Vert.Vertrag_ID = Arb.Vertrag_ID
       
        print @i
        SET @i = @i +1
    END
     
    --select * from tagesuebersicht
     
    
    Wäre super, wenn ihr mir nochmal helfen könntet.

    VG Ruediger
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Bitte sowas vermeiden, das ist ja hochgradig redundant. Aus einem Datum die KW, den Monat und den Wochentag zu berechnen ist erheblich sinnvoller als dieses zu speichern.
    Aus solch einer Tabelle und einer Tabelle der Mitarbeiter eine Tabelle je Mitarbeiter und Tag zu machen auch.

    Auch wenn ich Dir mit M$SQL nicht weiter helfen kann: das, was Du machst, schmerzt erheblich.
     
  11. Ruediger007

    Ruediger007 Benutzer

    Du hast natürlich recht, die Tagesübersicht habe ich schon reduziert bekommen. Bei der anderen Tabelle weiss ich noch nicht, wie ich die Binden soll, ohne die Mitarbeiter_ID. Mal sehen, ob die Redundanz da noch raus bekomme. Danke für den Hinweis. Des weiteren habe ich das andere Problem zum Glück auch endlich in den Griff bekommen.
     
  12. Tommi

    Tommi Datenbank-Guru

    Hi,

    deine Tabelle Tagesübersicht würde ich allerdings ohne Angabe der Mitarbeiter als eigenständige Tabelle stehen lassen.
    Da du diese Tabelle ja für Auswertungen nutzt, ist es hier auch völlig legitim bereits errechnete Werte direkt hinen zu schreiben, damit diese nicht bei jeder Abfrage neu berechnet werden.

    Das Arbeiten mit dieser Tabelle, in der die Werte bereits ermittelt enthalten sind, bietet für Auswertunge zwei erhebliche Vorteile:
    1. es kann hier ein Index erstellt werden, mit dem die Abfrageperformance deutlich erhöht werden kann.
    Dieser kann entpsrechend die vorliegenden Werte direkt mitliefern
    2. Eine solche Tabelle erleichtert es, Gruppierungen über Zeiträume vorzunehmen (Jahr, Monat, KW) und entsprechende Grenzwerte gleich mit zu ermitteln.
    (Anfang oder Ende eines Monats bzw. auch eine KW - was manchmal wichtige Informationen für Abfrageverknüpfungen sind)

    Die Angaben der Mitarbeiter kannst du dann mit einem CROSS JOIN hinzufügen.

    Ein Beispiel (in eckigen Klammern stehen optionale Statements):

    Code:
    SELECT [...]
    FROM dbo.Tagesübersicht TU
    CROSS JOIN dbo.Mitarbeiter Mit
    LEFT OUTER JOIN dbo.Vertrag Vert
        ON Mit.MA_ID=Vert.MA_ID
        [AND TU.Datum BETWEEN Vert.Datum_Von AND ISNULL(Vert.Datum_Bis, GETDATE()]
    LEFT OUTER JOIN dbo.ZeitenTabelle Arb
        ON Arb.Vertrag_ID=Vert.Vertrag_ID
        AND Arb.Datum=Vert.Datum (--> ??)
    [WHERE TU.Datum BETWEEN @vonDatum AND @bisDatum]

    Eine Bearbeitung über eine Schleife ist aus meiner Sicht nicht notwendig.

    Viele Grüße,
    Tommi
     
  13. Ruediger007

    Ruediger007 Benutzer

    Hi Tommi!

    Erstmal herzlichen Dank für die erneute sehr hilfriche Unterstützung! Ich habe alles umgebaut und es läuft einwandfrei. Ich habe noch eine Verständnisfrage.

    Könntest du mir vielleicht den Punkt noch etwas erläutern, ich verstehe ihn nämlich nicht so richtig. Meinst du damit, dass ab dem Moment, wo ich den Cross Join durchführe die Daten in der neuen Tabelle an einen Index gebunden sind und somit alle Abfragen auf die Tabelle schneller durchgeführt werden können?

    Beste Grüße
    Ruediger
     
  14. Tommi

    Tommi Datenbank-Guru

    Hi,

    ein Index ist Bestandteil einer Tabelle und muss für diese explizit angelegt werden.
    Um es mal einfach auszudrücken ist ein Index ein sortierter Katalog, über den Tabelleneinträge schnell und sicher identifiziert werden können.

    Indizes sind ganz wichtiger Bestandteil bei der Implementierung von Datenbanken.
    Genauere Informationen hierzu bekommst du natürlich aus dem Internet.

    Ich habe hier ein paar interessante Links, in denen das Thema Indizes noch einmal deutlicher beschrieben wird:
    http://www.szweb.de/Datenbankperformance/Indexplanung-Seite-225.html
    http://msdn.microsoft.com/de-de/library/ms190197(v=sql.105).aspx
    http://msdn.microsoft.com/de-de/library/ms188783(v=sql.90).aspx

    und natürlich

    http://de.wikipedia.org/wiki/Datenbankindex (dies aber nur Bedingt - kein guter Artikel, gibt aber ein wenig Überblick)

    Aber generell ging es mit eher darum die Vorteile einer statischen Tabelle mit bereits vorberechneten Daten aufzuführen.
    Du wirst für deine kleine Auswertung einen solchen Index nicht benötigen, aber es ist natütlich wichtig zu wissen, dass diese Möglichkeiten vorhanden sind!

    Ob du wirklich einen Index für eine Abfrage benötigst, kannst du aus dem Ausführungsplan ermitteln, den du die im SQL Server Management Studio anzeigen lassen kannst.
    Hier wird ein fehlender Index sogar mit einer CREATE-Befehl angezeigt und vorgeschlagen.


    Viele Grüße,
    Tommi
     
  15. ukulele

    ukulele Datenbank-Guru

    Im wesentlichen kann man sagen ein Index auf einer bestimmten Spalte macht immer dann Sinn, wenn du im WHERE Teil oder im ORDER BY Teil einer Abfrage Bezug auf die Werte einer Spalte nimmst und die Menge der Daten nicht unerheblich ist. Wenn du deine Abfrage mal mit mehreren kompletten Jahren testest solltest du das relativ schnell herausfinden können.
     
    akretschmer gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden