T-SQL: Kopieren eines Datensatzes mit Relationen via Stored Procedure

Moin Experten,

ich habe mal eine Frage zum Kopieren. Anbei mal ein analoges Beispiel mit Kundendaten, um das Problem prinzipiell zu erklären:

• Ich habe eine Tabelle mit Firmen.
• 1 zu n da drunter hängen die Kontaktpersonen
• An den Kontaktpersonen hängen wiederum 1-n die Telefonnummern

Nun möchte ich eine Firma (ID = 100) mit alle Kontaktpersonen und deren Telefonnummern 1:1 mit einem Aufruf einer Stored Procedure (also EXEC uspCopyFirm (100)) kopieren.

Ich muss ja in den kopierten Kontaktpersonen und Telefonnummern jeweils die neuen Fremdschlüssel schreiben.

Gibt es dafür eine smartere Lösung als mit zwei cursors in der usp zu arbeiten?

Gruß und Danke, Henning

 

Ich arbeite nicht mit TSQL, aber so ungefähr ist das Prinzip:

Pseudocode

Code:

proc xy(source_id bigint)
begin
insert into firma
(< feldliste >)
(select < feldliste > from forma where id = :source_id)
return id into :new_id
insert into kontaktperson
(ref_firma_id, < feldliste >)
(select :new_id,< feldliste > from kontaktperson where ref_firma_id = :source_id)
end

 

Hi und Danke erstmal.
Das funktioniert bei zwei Ebenen top. Wenn jetzt aber die dritte Ebene (-> Telefonnummern) dazu kommt, dann verliere ich die Zuordnung zur (neuen) Kontaktperson. Außer ich laufe halt über einen Cursor durch jede einzelne Kontaktperson.
Das müsste auch funktionieren, dachte aber, es gäbe vll. eine Abkürzung

 

Ja, ich habe den Twist übersehen, sorry.
Kommt drauf an, was die Anwendung sonst so braucht, wenn man eh auch einzelne Konaktpersonen kopieren können soll, dann noch dafür eine SP.
Im Select gleich die Telefonnummern mit holen (Aggregat) und Kontaktperson dann per SP einfügen.

 

Ja, so werde ich es wohl machen. Wie gesagt, suchte halt eine Abkürzung ...
Eine eine SP werde ich für die Kontaktpersonen nicht bauen, aber egal.
Geht übrigens nicht um Kontaktpersonen, sondern eine Regel- Engine. Das mit den Konatktpersonen war nur zur verdeutlichung.

 

Hallo Henning,

das ist eine allgemeine Migrations-Aufgabe (so verstehe ich das zumindest).

Da ich kein Cursor-Freund bin, würde ich davon eher abraten, wenn es nicht erforderlich ist - und das ist hier nicht notwendig.
Da du eh planst eine Prozedur zu erstellen, kannst du das auch sauber mit temporären Tabellen lösen.

Meine Vorgehensweise wäre wie folgt:
1. letzte ID in der Tabelle für Kontaktpersonen ermitteln und inkrementieren
2. temporäre Tabelle mit gleichem Aufbau wie die Tabelle der Kontaktpersonen erstellen und zusätzlich die ID-Spalte in der temporären Tabelle als IDENTITY mit Start der ermittelten ID anlegen (alte ID mitnehmen!)
3. die zu kopierenden Daten aus der Tabelle für Kontaktpersonen in die temporäre Tabelle einfügen.

In der temporären Tabelle liegen nun die zu kopierenden Daten mit bisheriger und mit neuer ID vor.

Das Gleiche machst du dann für die nächste abhängige Tabelle - bei deinem Beispiel die Telefonnummer - und beziehst die neue Kontaktpersonen-ID aus der temporären Tabelle, die du ja auf die bisherigen Daten über die alte Kontaktpersonen-ID joinen kannst.

Wenn alles ermittelt ist schiebst du die Daten, die in den temporären Tabellen liegen, in die entsprechenden Zieltabellen (natürlich mit den neuen IDs).

Um sicher zu gehen, dass in der Zwischenzeit keine manuellen Änderungen oder Eintragungen erfolgen kannst du am Anfang der Prozedur den ISOLATION LEVEL SERIALIZABLE setzen (am Ende der Prozedur wieder ausschalten).
TRY-CATCH mit TRANSACTION und ROLLBACK im Fehlerfall solltest du auch verwenden - dann machst du auch nix kaputt.

Viele Grüße,
Tommi

 

Verdeutlichung bzw. Vereinfachung find ich gut, vielleicht nicht so gelungen die Abstraktion. Hat die Engine eine feste Tiefe von 3 oder sind das Unterelemente einer Regel?

Also da es eine Regelengine ist, ist es vielleicht keine Migrationsaufgabe, sondern bloß die Kopie einer Regel.
Letzte ID ermitteln, Identity Spalte in .. temporärer Tabelle, Zieltabelle Identity Wert anpassen, puh, da denke ich an mySQL Gewürge. Es ist sicher vorzuziehen, große Datenmengen per SQL in einem Schwung zu bewegen, statt kleinteilig von record zu record zu hüpfen, aber so ein Zeug würde ich dann doch nicht dafür auflegen. Anhand der Aufgabe scheint es ja hier auch weniger um absolute Massenperformance zu gehen.

Wenn man schon ein größeres Rad drehen will, dann vielleicht mit Sequenzen und einem(1) Cursor. Sequenzen kann ja MSSQL mittlerweile auch.*
Also die gesamte Datenmenge mit einem Statement in einen Cursor selektieren und dabei gleich Sequenzen für die neuen ID verwenden, den Cursor dann durch laufen und neue Daten 1:1 auf der jeweilige Ebene in die zugehörige Tabelle einfügen. Setzt natürlich voraus, dass eine Umstellung auf Sequenzen möglich/gewollt ist (oder zufällig schon vorhanden, sinnvoller und flexibler finde ich die sowieso, siehe dieser Vorschlag)
Einfach Erweiterbar durch Select SQL Ergänzung auf neue Tabellen und zusätzliche Inserts in der Cursor Loop.

Isolation Level, Transaction, Rollback bekommt man m.E. alles dazu, wenn man außen eine SP einsetzt, da würde ich keine extra Nägel für einkloppen.*

Wenn der eine Cursor stört, dann könnte man noch das Verfahren von Henning mixen und die Identity Geschichten per Sequenz machen.

*wie gesagt, hab mit MSSQL wenig praktische Erfahrung.

 

Moin zusammen,

Danke erstmal. Es handelt sich hier um eine Komfort- Funktion, mit der der Anwender manuell das Kopieren einer Rule anstoßen kann.

Wenn es interessiert, hier das Tool:

In Summe über alles sind selten mehr 80 Datensätzen zu kopieren (max. vll. 300). Performance ist quasi egal. Die Rule hat fix zwei Unterebenen (analog zum Beispiel mit den Kontaktpersonen).

Die ganzen beschrieben Lösungen werden funktionieren, ich suchte halt eine Abkürzung. So als Anwender für diese ja oft vorkommende Aufgabe würde ich mir wünschen, ich könnte einen view über die drei Tabellen bauen und mit einem Insert-Select wäre das gelöst. Hab ich noch nicht probiert, aber kann das funktionieren? Vermutlich Wunschdenken, aber ich meine bei Access wäre sowas möglich (wobei ich keine Diskussion über Access anfangen möchte ...)

Gruß, Henning

 

würde man in PostgreSQL mittels wCTE (writeable Common Table Expressions) als 1 SQL-Statement hinbekommen, aber meines Wissens nach kann dies keine andere DB.

 

Bin leider an MS- SQL gebunden - werde das mit dem Join über drei Tabelle aber dennoch mal probieren.
Kann klappen, wenn nciht, bin ich zumindest schlauer

 

So, läuft.
Eine Temp- Tabelle für die Kontaktpersonen (um im anlogen Beispiel zu bleiben), dann darauf einen Cursor und jede Kontaktperson einzeln angelegt (Insert - Select). Dann neue ID für die Kontaktperson ermittelt und ebenfalls mit einem (Insert - Select) die Telefonnummer angelegt (mit neuer Kontaktperson ID als FK).

Der Versuch mit einem View über drei Tabellen und dann ein (Insert - Select) schlägt fehl (war zu erwarten).

Gruß und Danke