PDA

Vollständige Version anzeigen : SQL ist leicht (4) - Aktualisierung einer Tabelle


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
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
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
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
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 (http://www.ms-office-forum.net/forum/showthread.php?t=289028)

semi12
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.

zaira
26.04.2015, 16:53
Lieber ebs17, vielen Dank für Deinen Tip - hat bestens geklappt.

mfg zaira