Wie schreibt man einen Insert Befehl mit hochgezähltem Indexwert in eine Tabelle?

[HPeters]

Hallo zusammen,
ich habe gerade folgendes Problem, wo mir der Lösungsansatz fehlt:

Ich habe eine Tabelle "Wartung" mit den beiden Spalten ID (Schlüsselfeld, Integer) und Seriennummer (varchar).
Dieser Tabelle möchte ich um die Datensätze (Seriennummern) ergänzen, die am Vortag ausgeliefert wurden. Die ID (das Schlüsselfeld) muss dabei bei jedem neuen Datensatz um eins inkrementiert werden.
Die Seriennummern, die am Vortag ausgeleifert wurden, habe ich per Select Befehl identifiziert.
Select SN
From Tabelle
Where ........

Damit ich nicht für jede ausgelieferte Seriennummer einen manuellen Insert Befehl schreiben und ausführen muss, möchte ich gerne ein Script haben, welches mir für jeden Datensatz, der aus meinem Select Befehl herauskommt einen Insert Befehl für die Tabelle Wartungen schreibt, der dann auch je Datensatz die ID um eins heraufzählt.
Kann mir da jemand Hilfestellung geben?

Vielen Dank.

Nachtrag: Das ganze soll unter T-SQL laufen (MS-SQL)

[HPeters]

Natürlich over nicht cover. DAs war ein Tippfehler.
Hier jetzt korrigiert:

Insert into Wartung (ID,Seriennummer)
Select (Select Max(Wartung.ID) from Wartung) + ROW_NUMBER () over (Order by Auslieferung.SN ASC) as ID,
       Auslieferung.Seriennummer
From   Auslieferung
left outer join Wartung on Auslieferung.Seriennummer= Wartung.Seriennummer
Where  Wartung.Seriennummer is NULL

[defo]

Hallo zusammen,

ich habe jetzt eine Lösung gefunden.

Prima! Hätte man drauf kommen können.
Eine Window Function ist zwar ein Hammer für das Problem, aber es sieht nach einem brauchbaren Workaround aus.
"cover" soll wahrscheinlich "over" heißen.

[HPeters]

Hallo zusammen,

ich habe jetzt eine Lösung gefunden.

Insert into Wartung (ID,Seriennummer)
Select (Select Max(Wartung.ID) from Wartung) + ROW_NUMBER () cover (Order by Auslieferung.SN ASC) as ID,
       Auslieferung.Seriennummer
From   Auslieferung
left outer join Wartung on Auslieferung.Seriennummer= Wartung.Seriennummer
Where  Wartung.Seriennummer is NULL

So funktioniert es.
Der Trick dabei ist, dass die im Select zusammengesuchten Daten, die ja in die Tabelle Wartung eingefügt werden sollen, eine Nummerierung (ROW_NUMBER) der Datensätze stattfindet, die dann auf die höchste ID (vor dem Hinzufügen der Datensätze) aufaddiert wird.
" Select Max(Wartung.ID) from Wartung) + ROW_NUMBER () cover (Order by Auslieferung.SN ASC) as ID"
Die Durchnummerierung der Datensätze erfolgt folgendermaßen: 1. Datensatz = 1; 2. Datensatz = 2; usw.
Und die ID, die in die Tabelle Wartung geschrieben werden soll, ergibt sich wie folgt: Max ID + 1 (für 1. Datensatz); Max ID +2 (für 2. Datensatz), usw.

Gruß HPeters

[defo]

Also ich hatte mich da wohl flasch ausgedrückt. Ich würde keinen geplanten Task von Windows benutzen, sondern eine zeitgesteuerten Auftrag im SQL-Server-Agent einrichten. Das habe ich bereits geamacht. Den Auftrag würde ich dann jede Nacht starten lassen.
..

Vielleicht hab ich Dich auch falsch verstanden.
Funktioniert das denn mit Limit 1?

Die Konfigurationsmöglichkeiten für den Server Agent kenne ich leider nicht bzw. es ist wirklich ewig her. Meine Erwartung wäre aber, dass er sowas können sollte.

[HPeters]

Also ich hatte mich da wohl falsch ausgedrückt. Ich würde keinen geplanten Task von Windows benutzen, sondern eine zeitgesteuerten Auftrag im SQL-Server-Agent einrichten. Das habe ich bereits geamacht. Den Auftrag würde ich dann jede Nacht starten lassen.

Eine SP, die nicht nur die Insertaufgabe erledigt, sondern zusätzlich einen Rückgabewert liefert, der die Anzahl der offenen Datensätze ausgibt, würdest Du auch hinbekommen oder?

Das mit dem Rückgabewerte, wüßte ich überhaupt nicht wie das geht. Zumal man ja mit diesem Rückgabewert dann die Ausführung des SQL_Server-Agent Auftrages unterdrücken bzw. anhalten müsste. Und dann am nächsten Tag irgend wie wieder aktivieren. Ob das da funktioniert ???
Zumal der Auftrag ja für jeden anstehenden einzelnen zu schreibenden Datensatz erneut gestartet werden müsste.

[defo]

Es gibt ein Prinzip, das nennt sich devide and conquer, kannst Du mal googlen.

Du hast selbst davon gesprochen, dass Du den Aufruf automatisieren willst. Also musst Du ja eine Vorstellung haben, wie das mit deinen Softwarewerkezuegen geht, als SQL>MSSQL StoredProcedure>MS Windows Aufgabenplaner.
Dein SQL Statement hast Du, häng ein Limit 1 an das Select Statement und Du bist fertig.
Dann kannst Du pro Taskplaneraufruf einen Datensatz verarbeiten.
Damit kannst Du nichts kaput oder falsch machen.
Wenn das läuft, kommt die Vervielfachung. Aber eins nach dem anderen. Würdest Du es bis hier her hinbekommen?

Eine SP, die nicht nur die Insertaufgabe erledigt, sondern zusätzlich einen Rückgabewert liefert, der die Anzahl der offenen Datensätze ausgibt, würdest Du auch hinbekommen oder?
Ich muss an der Stelle sagen, dass ich seit längerer Zeit weder MSSQL noch Windows Taskplaner aktiv nutze und damit nur bedingt aktuelle Ratschläge dazu geben kann.

[HPeters]

Die Automatisierung muss halt automatisch oft genug aufgerufen werden. Dazu müsste die Automatik von der Einzeloperation einen Rückgabewert bekommen, der sagt, ob es noch mal laufen muss.

Puh, wieder was neues. Wie setzt man denn so was um?

[fanderlf]

Ich finde den Vorschlag von defo auch sehr gut. Das wäre vermutlich auch das was ich gemacht hätte in dem Fall.

[defo]

Also das Script auf 1 zu limitieren ist ja auch nicht die Lösung,

Wenn Du es automatisierst, mit einem Cron Job bzw. eher mit einem Scheduler Task unter Windows, dann kannst Du auch einen Mehrfachaufruf automatisieren.
Jeder Aufruf verarbeitet nur einen Datensatz, damit erreichst Du, dass Dein Script sauber läuft und selbst wenn Dein Kollege nachschicht macht und auch was einfügt alles okay wäre. (Soweit das mit dem Verfahren max(id), das ja auch der Hersteller nutzt eben möglich ist)
Die Automatisierung muss halt automatisch oft genug aufgerufen werden. Dazu müsste die Automatik von der Einzeloperation einen Rückgabewert bekommen, der sagt, ob es noch mal laufen muss.

Das ist alles nicht schön und allein der Thread hier ist schon mehr Arbeit, als es den Hersteller kosten würde, seine ID richtig zu verwalten. Aber offenbar gibt es ja keine Entgegenkommen.

[HPeters]

Also das Script auf 1 zu limitieren ist ja auch nicht die Lösung, da wir ja jeden Tag eine unterschiedliche Anzahl von Seriennummern ausliefern. Und dann jemanden wer weiß wie oft ein Script starten zu lassen, ist auch nicht die Lösung. Ich möchte das ja automatisiert jede Nacht einmal starten und dann sollen ja alle Datensätze in der Tabelle Wartung angelegt werden.

Also bin ich doch wieder bei meiner bereits zuvor beschrienen Methode (wie auch immer ich das umsetzen kann):

Irgend wie müsste man eine äußere Schleife programmieren, die die betroffenen Seriennummern fängt, und eine innere Schleife, die das Insert enthält und dazu auch bei jedem Duchlauf den Max Wert der ID aus der Tabelle Wartung ausliest, den um ein hochzählt und mit der Seriennummer aus der äußeren Schleife an das Insert übergibt.

Oder doch eine Lösung mit einem Cursor?
Ich wäre erfreut über Lösungsansätze.

Gruß HPeters

[defo]

Da habe ich Vorstellungsprobleme.

Ja, Du hast Recht, das Max(id) Verfahren ist tückisch. Aber das liegt ja auch schon in der Verwendung herstellerseitig. Das kann man schon mal nicht beeinflussen.

Wenn das gegebene Script mit Limit 1 eingeschränkt wird, muss es nur ein Planer so lange aufrufen, bis es leer läuft. Das ist nicht schön, aber es würde gehen.
Letztlich spricht ja auch nichts absolut gegen eine Sperre.
Weiter könnte man die einzel Inserts auch in autonome Transaktionen kapseln, das oll MSSQL ja können. Dann greift bei jedem Max(id) aufruf der aktuelle Stand.

[HPeters]

Hallo zusammen,
hallo defo,
hallo Ralf,
hallo fanderlf,

vielen Dank für Eure Antworten.
Ich bin sicherlich nicht so tief in der Matrie wie Ihr es anscheinend seit.
Ich verstehe die Sorge, das es vermieden werden soll, das 2 verschiedene Quellen (einmal mein Script, das ich noch nicht habe und zum Anderen die Hersteller Software) gleichzeitig Datensätze in die Tabelle Wartunge schreiben sollten. Das würde unweigerlich zu einer Primary Key Verletzung führen. Es ist aber sowieso nur einem User per Berechtigung erlaubt, die Datensätze in der Tabelle Wartung anzulegen und der sitz noch bei mir im Büro. Da ist das Risiko schon sehr minimal. Zumal ich ja auch verhabe, das Script per geplantem Task in der Nacht laufen zu lassen, da arbeitet der Kollege ja nicht. Somit in meinem Fall unkritisch.

Jetzt stehen aktuell die beiden Vorschläge Cursor bzw. While-Schleife im Raume.

Also mit einem Cursor habe ich noch nie was zu tuen gehabt. Völliges Neuland für mich.

Also wenn Ihr mir da noch ein paar hinweise oder Musterbeispiele geben könntet, würde ich es versuchen.

Gruß HPeters

[Ralf Jansen]

M.E. müsste beides ohne Sperren funktionieren, da es in einer Transaktion abläuft und geschützt ist.

Da habe ich Vorstellungsprobleme. Auch wenn select max(id) und der Insert auf die Wartung Tabelle in einer Transaktion sind sehe ich nicht warum nicht ein anderer "select max(id)" auf die Wartung Tabelle dazwischen kommen kann wenn nicht der select max(id) die Tabelle explizit sperrt.
Laut Aussage ist ja sein Skript nicht der einzige Code der das mit der max(Id) so anstellt sondern der Hersteller Code selbst auch irgendwie. Und diesen Code hat er vermutlich nicht wirklich unter Kontrolle.

Ich bin mir nicht mal sicher das der gezeigte Insert ... Select vom TE da wirklich Threadsafe ist. Es ist ja mehr oder weniger Zufall das im Insert und im Select die gleichen Tabellen benutzt werden. Ich bezweifle das es da einen automatischen Sperrmechanismus gibt. Der wäre meist kontraproduktiv.

Also kann man das SQL Statement und die Variablenmechnik aus meinem Link nicht mischen in MSSQL?

Man kann auch Tabellenvariablen benutzen (könnte man im allgemeinen auch temporäre Tabelle nennen) und darin das Ergebnis des Selects reinschieben. Gefühlt wenn ich darin schon die IDs ~berechne~ braucht es aber eine längere Exclusive Sperre anstatt viele kurzer. Letzteres halte ich in den meisten Szenarien für wünschenswerter und denke ist mit einem Cursor leichter umsetzbar.

[defo]

Also kann man das SQL Statement und die Variablenmechnik aus meinem Link nicht mischen in MSSQL?
Alternativ eine SP die automar nur jeweils einen Satz einfügt und das solange loopen bis keine Datensätze mehr vorhanden sind.
M.E. müsste beides ohne Sperren funktionieren, da es in einer Transaktion abläuft und geschützt ist.

@TE, Autoincrement ist ein Datentyp der automatisch fortlaufende Nummern produziert ohne das sich irgendjemand um diese Werte kümmert.
Bei Erstellung ist es quasi nur ein anderes Wörtchen in der Tabellendefinition, also ein Klacks für den Hersteller.
Bei Änderung muss man leider etwas aufwändiger dran gehen, ist aber auch problemlos machbar.