Grundlagen - SQL ist leicht (4) - Aktualisierung einer Tabelle - MS-Office-Forum

ebs17 · 29.10.2013, 11:50

Aktualisierung einer Tabelle über eine andere Tabelle

Häufig kommt es vor, dass man eine Datentabelle in Form einer Textdatei, Exceltabelle oder Datenbanktabelle erhält, die in einem externen System (Wawi) erzeugt wurde (nachfolgend Quelltabelle genannt). Mit den enthaltenen Daten soll nun eine Tabelle in der eigenen Accessdatenbank (Zieltabelle) aktualisiert werden.

Wenn nun die Quelltabelle den kompletten benötigten Datenbestand enthält, könnte man
a) die Zieltabelle löschen und die Quelltabelle einfügen,
b) die Zieltabelle per Löschabfrage leeren und anschließend per Anfügeabfrage neu füllen. In diesem zweiten Fall blieben in der Zieltabelle eigene Einstellungen wie angelegte Indizes und Gültigkeitsregeln erhalten.

In beiden Fällen ist das Löschen in Praxis nur ein Setzen eines Löschvermerks und kein physisches Löschen dieser Daten. Die neuen Daten werden in der Datenbank aber angefügt und führen zu einer Vergrößerung der Datenbank (Aufblähen). Bei recht großen Datenmengen oder einer häufigen Wiederholung eines solchen Vorgangs kann man hier zu kritischen Zuständen kommen, die sich in einer Verlangsamung von Vorgängen bis hin zum Datenbankabsturz wegen Überschreitens der maximalen Dateigröße von 2 Gigabyte äußern können.

Eine reale Speicherfreigabe und damit die Rückführung der Dateigröße auf das notwendige Maß erzielt man durch ein Komprimieren der Datenbank, in der die Zieltabelle liegt. Allerdings sollte man diese Maßnahme nicht in dem Moment durchführen, wo andere Benutzer auf diese Datenbank zugreifen. Bei Mehrnutzer- und 24-Stundenbetrieb hätte man da also Zusätzliches zu beachten.

Eine zusätzliche Überlegung: Wenn Quell- und Zieltabelle je etwa 1 Million Datensätze enthalten und sich beispielsweise nur sehr geringfügig unterscheiden (1 geänderter Datensatz und 1 neuer Datensatz), wäre das komplette Löschen und Neuanlegen von einer Million Datensätzen ein riesiger (datenbankinterner) Aufwand, das Ändern eines Datensatzes zuzüglich das Anfügen eines Datensatzes dagegen naheliegend und sehr sparsam, wenn man diese Datensätze einfach ermitteln kann.

Enthält nun aber die Quelltabelle nicht den kompletten Datenbestand oder ist die Zieltabelle über Beziehungen mit anderen Tabellen verknüpft (was ein komplettes Löschen ausschließt), muss man dann differenzierter herangehen und würde dabei auch die oben genannte Aufblähproblematik entschärfen.

Eine Aktualisierung der Zieltabelle teilt sich dann in drei Teilaufgaben (sinnvoll in dieser Reihenfolge):

1) (wird oft nicht auszuführen sein) Löschen der nicht mehr benötigten Datensätze (erkennbar dadurch, dass diese in der Quelltabelle nicht vorhanden sind). Ersatzweise wird man Datensätze zum Vollständighalten der Historie nicht löschen, sondern darin einen Löschvermerk setzen, über den dann in der normalen Verwendung die aktuellen Daten gefiltert werden.
2) Aktualisieren der bestehenden Datensätze (in der Quelltabelle gibt es entsprechende Komplementär-Datensätze).
3) Anfügen der neuen Datensätze. Hier sollte man darauf achten, nur neue Datensätze anzufügen. Hier bietet sich eine integrierte Inkonsistenzprüfung an. Es besteht zwar die Möglichkeit, per Anfügeabfrage alle Datensätze der Quelltabelle der Zieltabelle zuzuweisen und die Abwehr der überflüssigen Datensätze einem eindeutigen Index zu überlassen. Jedoch: Damit entsteht wiederum ein erhöhter Aufwand (größeres Recordset), es kann auch hier ein Aufblähproblem entstehen, und nicht zuletzt sind Indexfehler auch Fehler. Fehler vermeidet man (in den überwiegenden Fällen) besser, statt sie bewusst zu erzeugen und dann zu ignorieren.

Als Modell zur Formulierung möglicher Abfragen, die die genannten Aufgaben erledigen können, haben Quell- und Zieltabelle hier je die Felder Key1 und Key2, die gemeinsam den Schlüssel für eine Identifizierung eines Datensatzes bilden, sowie zwei Wertfelder (Value1 und Value2) und ein Feld, das einen Zeitstempel (Datum+Zeit der Anlage oder letzten Änderung) enthält.

(1a) Löschen

Code:

DELETE 
    FROM 
    Zieltabelle AS Z 
WHERE 
    NOT EXISTS 
        (
            SELECT 
                NULL 
            FROM 
                Quelltabelle AS Q 
            WHERE 
                Q.Key1 = Z.Key1 
                    AND 
                Q.Key2 = Z.Key2 
        )

(1b) Setzen Löschvermerk

Code:

UPDATE 
    Zieltabelle AS Z 
SET 
    Z.Loeschvermerk = True, 
    Z.Timestampfield = Now() 
WHERE 
    NOT EXISTS 
        (
            SELECT 
                NULL 
            FROM 
                Quelltabelle AS Q 
            WHERE 
                Q.Key1 = Z.Key1 
                    AND 
                Q.Key2 = Z.Key2 
        )

(2) Aktualisieren bestehender Datensätze

Code:

UPDATE 
    Zieltabelle AS Z 
        INNER JOIN Quelltabelle AS Q 
        ON Q.Key1 = Z.Key1 
        AND Q.Key2 = Z.Key2 
SET 
    Z.Value1 = Q.Value1, 
    Z.Value2 = Q.Value2, 
    Z.Timestampfield = Now() 
WHERE 
    Z.Timestampfield < Q.Timestampfield 
        OR 
    (
        Z.Value1 > Q.Value1 
            OR 
        Z.Value1 < Q.Value1 
    )

Mit passenden zusätzlichen Kriterien (zwei denkbare werden im WHERE-Teil gezeigt) lassen sich die Datensätze auf diejenigen beschränken, wo sich wirklich etwas geändert hat. Hier müsste man stärker konkrete Bedingungen eines vorliegenden Falls beachten. Insgesamt dient das dann wieder der Aufwandsbegrenzung.

(3) Anfügen nur neuer Datensätze

Code:

INSERT INTO 
    Zieltabelle(
        Key1, 
        Key2, 
        Value1, 
        Value2, 
        Timestampfield 
    ) 
SELECT 
    Q.Key1, 
    Q.Key2, 
    Q.Value1, 
    Q.Value2, 
    Now() 
FROM 
    Quelltabelle AS Q 
        LEFT JOIN Zieltabelle AS Z 
        ON Q.Key1 = Z.Key1 
            AND 
        Q.Key2 = Z.Key2 
WHERE 
    Z.Key1 Is Null

ebs17 · 26.11.2013, 14:24

Wenn der Import dann nicht nur auf eine Zieltabelle, sondern auf mehrere Tabellen im Datenmodell erfolgen soll, könnte man dann folgenden Ansatz hinzuschalten:
Importtabelle in m:n-Beziehung auflösen

24.01.2014, 07:54

Hier wird sehr schön gezeigt, wie man bei der Replace-Methode Teile des Suchmusters (Pattern) als Bestandteil des Ersetzungsmusters verwenden kann.

26.04.2015, 16:53

Lieber ebs17, vielen Dank für Deinen Tip - hat bestens geklappt.

mfg zaira

ebs17 · 23.02.2021, 18:44

Bezugnehmend auf #2: DBWiki und somit der angeführte Link sind derzeit nicht mehr erreichbar.
Daher habe ich das Beispiel, wie man eine Report-/Importtabelle, präziser deren Inhalte, in die Tabellen einer vorhandenen m:n-Beziehung aufteilen kann mittels Anfügeabfrage pro Tabelle, angefügt.

bernardand · 25.05.2021, 16:30

Wenn der Import dann nicht nur auf eine Zieltabelle, sondern auf mehrere Tabellen im Datenmodell erfolgen soll, könnte man dann folgenden Ansatz hinzuschalten

29.10.2013, 11:50	#1
ebs17 MOF Guru Registrierung: 04.02.2006 Ort: Reinsberg / Sa. nahe Bergstadt Freiberg Beiträge: 32.784 Karma: Modifikator: 69	Grundlagen - SQL ist leicht (4) - Aktualisierung einer Tabelle Aktualisierung einer Tabelle über eine andere Tabelle Häufig kommt es vor, dass man eine Datentabelle in Form einer Textdatei, Exceltabelle oder Datenbanktabelle erhält, die in einem externen System (Wawi) erzeugt wurde (nachfolgend Quelltabelle genannt). Mit den enthaltenen Daten soll nun eine Tabelle in der eigenen Accessdatenbank (Zieltabelle) aktualisiert werden. Wenn nun die Quelltabelle den kompletten benötigten Datenbestand enthält, könnte man a) die Zieltabelle löschen und die Quelltabelle einfügen, b) die Zieltabelle per Löschabfrage leeren und anschließend per Anfügeabfrage neu füllen. In diesem zweiten Fall blieben in der Zieltabelle eigene Einstellungen wie angelegte Indizes und Gültigkeitsregeln erhalten. In beiden Fällen ist das Löschen in Praxis nur ein Setzen eines Löschvermerks und kein physisches Löschen dieser Daten. Die neuen Daten werden in der Datenbank aber angefügt und führen zu einer Vergrößerung der Datenbank (Aufblähen). Bei recht großen Datenmengen oder einer häufigen Wiederholung eines solchen Vorgangs kann man hier zu kritischen Zuständen kommen, die sich in einer Verlangsamung von Vorgängen bis hin zum Datenbankabsturz wegen Überschreitens der maximalen Dateigröße von 2 Gigabyte äußern können. Eine reale Speicherfreigabe und damit die Rückführung der Dateigröße auf das notwendige Maß erzielt man durch ein Komprimieren der Datenbank, in der die Zieltabelle liegt. Allerdings sollte man diese Maßnahme nicht in dem Moment durchführen, wo andere Benutzer auf diese Datenbank zugreifen. Bei Mehrnutzer- und 24-Stundenbetrieb hätte man da also Zusätzliches zu beachten. Eine zusätzliche Überlegung: Wenn Quell- und Zieltabelle je etwa 1 Million Datensätze enthalten und sich beispielsweise nur sehr geringfügig unterscheiden (1 geänderter Datensatz und 1 neuer Datensatz), wäre das komplette Löschen und Neuanlegen von einer Million Datensätzen ein riesiger (datenbankinterner) Aufwand, das Ändern eines Datensatzes zuzüglich das Anfügen eines Datensatzes dagegen naheliegend und sehr sparsam, wenn man diese Datensätze einfach ermitteln kann. Enthält nun aber die Quelltabelle nicht den kompletten Datenbestand oder ist die Zieltabelle über Beziehungen mit anderen Tabellen verknüpft (was ein komplettes Löschen ausschließt), muss man dann differenzierter herangehen und würde dabei auch die oben genannte Aufblähproblematik entschärfen. Eine Aktualisierung der Zieltabelle teilt sich dann in drei Teilaufgaben (sinnvoll in dieser Reihenfolge): 1) (wird oft nicht auszuführen sein) Löschen der nicht mehr benötigten Datensätze (erkennbar dadurch, dass diese in der Quelltabelle nicht vorhanden sind). Ersatzweise wird man Datensätze zum Vollständighalten der Historie nicht löschen, sondern darin einen Löschvermerk setzen, über den dann in der normalen Verwendung die aktuellen Daten gefiltert werden. 2) Aktualisieren der bestehenden Datensätze (in der Quelltabelle gibt es entsprechende Komplementär-Datensätze). 3) Anfügen der neuen Datensätze. Hier sollte man darauf achten, nur neue Datensätze anzufügen. Hier bietet sich eine integrierte Inkonsistenzprüfung an. Es besteht zwar die Möglichkeit, per Anfügeabfrage alle Datensätze der Quelltabelle der Zieltabelle zuzuweisen und die Abwehr der überflüssigen Datensätze einem eindeutigen Index zu überlassen. Jedoch: Damit entsteht wiederum ein erhöhter Aufwand (größeres Recordset), es kann auch hier ein Aufblähproblem entstehen, und nicht zuletzt sind Indexfehler auch Fehler. Fehler vermeidet man (in den überwiegenden Fällen) besser, statt sie bewusst zu erzeugen und dann zu ignorieren. Als Modell zur Formulierung möglicher Abfragen, die die genannten Aufgaben erledigen können, haben Quell- und Zieltabelle hier je die Felder Key1 und Key2, die gemeinsam den Schlüssel für eine Identifizierung eines Datensatzes bilden, sowie zwei Wertfelder (Value1 und Value2) und ein Feld, das einen Zeitstempel (Datum+Zeit der Anlage oder letzten Änderung) enthält. (1a) Löschen Code: DELETE FROM Zieltabelle AS Z WHERE NOT EXISTS ( SELECT NULL FROM Quelltabelle AS Q WHERE Q.Key1 = Z.Key1 AND Q.Key2 = Z.Key2 ) (1b) Setzen Löschvermerk Code: UPDATE Zieltabelle AS Z SET Z.Loeschvermerk = True, Z.Timestampfield = Now() WHERE NOT EXISTS ( SELECT NULL FROM Quelltabelle AS Q WHERE Q.Key1 = Z.Key1 AND Q.Key2 = Z.Key2 ) (2) Aktualisieren bestehender Datensätze Code: UPDATE Zieltabelle AS Z INNER JOIN Quelltabelle AS Q ON Q.Key1 = Z.Key1 AND Q.Key2 = Z.Key2 SET Z.Value1 = Q.Value1, Z.Value2 = Q.Value2, Z.Timestampfield = Now() WHERE Z.Timestampfield < Q.Timestampfield OR ( Z.Value1 > Q.Value1 OR Z.Value1 < Q.Value1 ) Mit passenden zusätzlichen Kriterien (zwei denkbare werden im WHERE-Teil gezeigt) lassen sich die Datensätze auf diejenigen beschränken, wo sich wirklich etwas geändert hat. Hier müsste man stärker konkrete Bedingungen eines vorliegenden Falls beachten. Insgesamt dient das dann wieder der Aufwandsbegrenzung. (3) Anfügen nur neuer Datensätze Code: INSERT INTO Zieltabelle( Key1, Key2, Value1, Value2, Timestampfield ) SELECT Q.Key1, Q.Key2, Q.Value1, Q.Value2, Now() FROM Quelltabelle AS Q LEFT JOIN Zieltabelle AS Z ON Q.Key1 = Z.Key1 AND Q.Key2 = Z.Key2 WHERE Z.Key1 Is Null __________________ ... oder ganz anders machen. Ein freundliches Glück Auf! Eberhard Abfrage-Performance ist kein Geheimnis SQL ist leicht --- Linksammlung zur Themenreihe

26.04.2015, 16:53	#4
zaira Beiträge: n/a Modifikator:	Quell-Tab in Ziel-Tab aktualisieren Lieber ebs17, vielen Dank für Deinen Tip - hat bestens geklappt. mfg zaira

Themen-Optionen
Druckbare Version zeigen Diese Seite per E-Mail verschicken
Ansicht
Linear-Darstellung Zur Hybrid-Darstellung wechseln Zur Baum-Darstellung wechseln

26.11.2013, 14:24	#2
ebs17 Threadstarter MOF Guru Registrierung: 04.02.2006 Ort: Reinsberg / Sa. nahe Bergstadt Freiberg Beiträge: 32.784 Karma: Modifikator: 69	Wenn der Import dann nicht nur auf eine Zieltabelle, sondern auf mehrere Tabellen im Datenmodell erfolgen soll, könnte man dann folgenden Ansatz hinzuschalten: Importtabelle in m:n-Beziehung auflösen __________________ ... oder ganz anders machen. Ein freundliches Glück Auf! Eberhard Abfrage-Performance ist kein Geheimnis SQL ist leicht --- Linksammlung zur Themenreihe

24.01.2014, 07:54	#3
semi12 Beiträge: n/a Modifikator:	Hier wird sehr schön gezeigt, wie man bei der Replace-Methode Teile des Suchmusters (Pattern) als Bestandteil des Ersetzungsmusters verwenden kann.

25.05.2021, 16:30	#6
bernardand Neuer Benutzer Registrierung: 25.05.2021 Beiträge: 1 Karma: Modifikator: 0	Wenn der Import dann nicht nur auf eine Zieltabelle, sondern auf mehrere Tabellen im Datenmodell erfolgen soll, könnte man dann folgenden Ansatz hinzuschalten

Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)