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)

[Christian Marquardt]

-> verschoben MSSQL

[defo]

Das Prinzip ist so:
insert into meinetabelle (zielspatenliste)
<mein select statement>

Dabei müssen die Spalten natürlich zueinander passen.

Um eine passende ID zu erzeugen, muss man verstehen, wie die ID der Zieltabelle normalerweise entstehen.
per Trigger
per autoincrement
per Sequenz
..

Ein entsprechender Ausdruck muss dann für die ID Spalte im Select Statement gewählt werden.
U.U. fällt die Spalte einfach weg, weil sie autark verarbeitet wird.

Ein solches Statement kann ohne TSQL ganz allein ausgeführt werden.

[HPeters]

Hallo defo,

also der ID Wert wird laut Hersteller über Max(id)+1 incrementiert.
Nur ich weiß nicht, wie ich das in das Insert Script mit einbinden kann, da ja bei jedem Insert in die Tabelle Wartung auch der ID Wert incrementiert werden muss.
Ich hatte das mal so probiert:

 
Insert into Wartung (ID,Seriennummer)
Select (Select Max(Wartung.ID)+1 from Wartung) as ID,
       Auslieferung.Seriennummer
From   Auslieferung
left outer join Wartung on Auslieferung.Seriennummer= Wartung.ID
Where  Wartung.ID is NULL
;

Leider versucht das Script bei der 2ten Auslieferung (also dann, wenn die 2te Seriennummer in die Tabelle eingetragen werden soll) dies immer mit der gleichen ID zu machen, was natürlich unweigerlich zu einem Fehler führt, da ja die ID in der Tabelle Wartung das Schlüsselfeld ist und somit eindeutig sein muss.
Wie kann ich das anders lösen?

[defo]

"..laut Hersteller.."?
Also ist das ein Fremdsystem in das Du schreibst? Musst Du wissen, ob das gut ist.

Dein Join im Select ergibt keinen Sinn. Die beiden Tabellen haben nichts miteinander zu tun.
Du brauchst das Maximum der Zieltabelle ID und deine Werte aus Auslieferung.

Wenn Du ein Insert machst und es keinen der von mir erwähnten Mechnismen gibt (Trigger...), ist auch eine Null Prüfung unnötig. DU liefest alle Werte, die inserted werden.

Die bestimmung der ID=Max(ID)+1 ist im übrigen relativ aufwendig, also langsam und ressourcenfressend. Die Nutzung einer Sequenz oder eines Triggers wäre sinnvoller. Hast Du mal selber geschaut, wie die Tabelle aufgebaut ist und welche Mechnismen zur ID Versorgung da sein könnten?

[HPeters]

Ja, es gibt eine Software, in der man die Datensätze auch händisch eintragen kann. Da wir aber täglich eine Vielzahl an Seriennummern ausliefern, würde das eine Ewigkeit dauern, die Datensätze über die Software in der Tabelle Wartung anzulegen.
Deshalb habe ich mich dazu entschieden, die Anlage der Datensätze täglich per Script machen zu wollen. Sowas kann man ja per geplantem Task anlegen, und hat damit dann keine Arbeit mehr.

Ja, der Hersteller hat mir gegenüber geäußert, das er die ID in der Tabelle Wartung auch per Max(Wartung.ID) +1 hochzählt. Deshalb habe ich das auch so übernommen.

Bezüglich dem Join gebe ich Dir Recht.
Da hatte ich mich vertippt. Das sollte natürlich heißen: From Auslieferung left outer join Wartung on Auslieferung.Seriennummer= Wartung.Seriennummer (anstatt Wartung.ID)

Also hier nich mal das korrigierte Script:

Insert into Wartung (ID,Seriennummer)
Select (Select Max(Wartung.ID)+1 from Wartung) as ID,
       Auslieferung.Seriennummer
From   Auslieferung
left outer join Wartung on Auslieferung.Seriennummer= Wartung.Seriennummer
Where  Wartung.Seriennummer is NULL

Dann mach der Join auch Sinn. Denn ich möchte ja nur die Seriennummern in die Tabelle Wartung eintragen, die noch keinen Eintrag bekommen haben. Und das fange ich doch mit dem Join und der Where Bedingung "Where Wartung.Seriennummer is NULL" ab. (da hatte ich vorher auch fälschlicherweise die ID drin).

Gruß

[defo]

Ok, in dem Statement fehlt nun ein IS in der where clause.

Das Problem bei diesem Verfahren der Bestimmung von max(id) ist der Transaktionskontext.
Es wird zwar richtig selektiert, aber das Ergebnis bleibt innerhalb der Transaktion konstant. Also nur das erste Insert bzw. der erste Datensatz wird richtig eingefügt.
Ich bin kein MSsQL Spezialist. Was Du bräuchtest wäre eine Funktion, selber steigende ID Werte basierend auf max(id) zu berechnen.
Das ist aber eine ziemliche Bastelei.

Ich würde mir lieber ein Go holen, einen Trigger oder Autoincrement zu verwenden und das nachrüsten. Dann kannst Du die Spalte ID einfach beim Insert und Select weglassen. Das muss natürlich kompatibel zur bestehenden Anwendung des herstellers gemacht werden.

Alternativ musst du das Script so aufbauen, dass es immer nur einen Datensatz einfügt und das per Planer so oft aufrufen, bis nichts mehr da ist.
Auch Bastelei.
Oder Du verwendest im Script Variablen, etwa wie hier:
https://www.mssqltips.com/sqlservert...g-an-identity/
Sektion: Using Variables To Update and Increment the Value by 1
Auch nicht wirklich nach meinem Geschmack.

[HPeters]

Also einen Trigger, oder ein Autoincrement (was auch immer das ist) werde ich nicht installiert bekommen. Da sperrt sich der Hersteller.

Den Link den Du mir geschickt hattest, den habe ich mir mal angeschaut. Aber so richtig, bringt mich der ja auch nicht weiter.

Irgend wie müsste man doch 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.

Gruß HPeters

[fanderlf]

In normalem SQL kannst Du so etwas nicht machen. In der Regel kennt SQL keine Reihenfolge der Datensätze mit Ausnahme von sortieren. Das einzige was Du machen könntest ist, dass Du pro Eintrag ein neues SQL Statement erzeugst und dieses sich dann die höchste ID holt und diese eins hoch zählt, ähnlich Deinem Beispiel oben.

Schleifen könntest Du in einem T-SQL Skript machen, sofern Du so etwas ausführen darfst. z.B. https://docs.microsoft.com/de-de/sql...e-transact-sql

[Ralf Jansen]

Du könntest eines Cursor mit deiner Abfrage der Seriennummern erzeugen und dann über den Cursor iterieren und je Datenzeile dann die Id ermitteln und den Insert abfeueren.

Das solltest du aber richtig sperren. Sobald du die max id ermittelt hast must du die Wartung Tabelle sperren damit keine andere Transaktion währenddessen das Gleiche macht was du in deiner Transaktion tust (also max id abfragen und dann eine potentiel doppelte id bekommen). Welcher Hersteller macht eigentlich so einen Mist mit max(id)? Oder ist das eine explizite SingleUser Anwendung?

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

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

[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

[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]

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