INSERT über mehrere Tabellen

verwendetes Datenbanksystem: MS SQL Server 2005 / 2008

Hallo,
ich habe ein Problem. Entweder stehe ich auf dem Schlauch oder es ist doch etwas komplizierter. Grundsätzlich gehe ich vom ersten aus =)

Zum Thema:
Ich will für das angehängte Datenbankschema einen Insert Befehl schreiben welcher mir alle Tabellen füllt. Also das auch die ID´s der zugehörigen Tabellen gefüllt werden. Sprich: Ich trage alle Daten für die Tabelle USERS und ADDITIONELL ein und möchte das am Ende alles richtig verknüpft ist. Das INSERT für USERS und ADDITIONELL sind mir klar. Aber wie schaffe ich es, dass während des INSERTS die AdditionalID mit der ID aus der ADDITIONAL gesetzt wird?

Ich bin dankbar für jeden Tipp X(

Benedikt

Aber wie schaffe ich es, dass während des INSERTS die AdditionalID mit der ID aus der ADDITIONAL gesetzt wird?

Warum wird es das nicht?
oder: warum ist es das nicht bereits? Weil du sagst, du trägst alles richtig ein.

verwendest du typisiertes Dataset oder or-mapper?

Aber wie schaffe ich es, dass während des INSERTS die AdditionalID mit der ID aus der ADDITIONAL gesetzt wird?
Warum wird es das nicht?
oder: warum ist es das nicht bereits? Weil du sagst, du trägst alles richtig ein.

verwendest du typisiertes Dataset oder or-mapper?

Oh. dann habe ich mich vielleicht falsch ausgedrückt. Also es klappt wenn ich es von Hand vergebe. Ich suche einen Insert Befehl den ich später "füttern" kann und der quasi alles automatisch macht.

Drücke ich mich verständlich aus? Weiss nicht wie ich es besser beschreiben soll 🤔 Ich bin noch gar nicht im Code sondern im SQL Server Management. Auch kein OR Mapper.

Benedikt

Hallo,

innerhalb eines INSERTs wird das nicht funktionieren. Du brauchst also mindestens Zwei. Eins für den USER und anschließend eins für die Tabelle ADDITIONAL. Im Groben also

DELCARE @UserID INT

SET @UserID = INSERT INTO USER (Forename, Surname, etc)
                       OUTPUT INSERTED.ID
                       VALUES ('Bla', 'Blubb', etc)

INSERT INTO ADDITIONAL (Email, [Wo ist die UserID?])
VALUES ('bal@blubb.de', @UserID)

Da ist noch ein kleiner Fehler im Design an sich. Ein User kann nur ein Additional haben? Eher umgekehrt oder? Ein User kann mehrere Additoonals haben. Denn die AdditionalID steht im User. Die Tabelle Additional sollte aber eher die UserID mit sich tragen. Deswegen habe ich das gezeigte Script gleich so abgeändert.

Und noch etwas zu deinem Schema/Design. Benenne die einzelnen ID Spalten immer mit den Tabellennamen davor. Also UserID, AdditonalID, AddressID, CityID und CountryID. Später wenn du mal über mehrere Tabellen joinen solltest, wirst du es dir selber danken. Denn man verliert dann fix den Überblick (IMHO).

Ebenfalls sollten Tabellen immer im Plural geschrieben werden, da eine Tabelle ja mehrere Daten enthält. Also Users, Adiitonals, Cities usw... Wobei die ID Spalte dann weiterhin im Singular benannt wird.

Ebenfalls sollten Tabellen immer im Plural geschrieben werden, da eine Tabelle ja mehrere Daten enthält.

Das lese ich aber sehr oft genau andersherum: Der Tabellenname sollte immer im Singular geschrieben werden; denn es ist einfacher, von einem "User" zu sprechen statt von einem "Datensatz aus Users".

Wobei man auf jeden Fall vermeiden sollte, reservierte Wörter wie USER als Tabellen- oder Spaltennamen zu verwenden.

Gruß Jürgen

Vielen Vielen Dank. Ich werde Deine Ratschläge beherzigen und alles so ausprobieren. Falls ich wieder Probleme habe melde ich mich wieder.

zur Additional: Ich dachte eigentlich, dass jeder User nur eine Additional hat. Aber jetzt wo ich drüber nachdenke könnte man dann die Daten auch direkt zu User schreiben. 🤔

Benedikt

Hallo,

innerhalb eines INSERTs wird das nicht funktionieren. Du brauchst also mindestens Zwei. Eins für den USER und anschließend eins für die Tabelle ADDITIONAL. Im Groben also

  
DELCARE @UserID INT  
  
SET @UserID = INSERT INTO USER (Forename, Surname, etc)  
                       OUTPUT INSERTED.ID  
                       VALUES ('Bla', 'Blubb', etc)  
  
INSERT INTO ADDITIONAL (Email, [Wo ist die UserID?])  
VALUES ('bal@blubb.de', @UserID)  
  

Da ist noch ein kleiner Fehler im Design an sich. Ein User kann nur ein Additional haben? Eher umgekehrt oder? Ein User kann mehrere Additoonals haben. Denn die AdditionalID steht im User. Die Tabelle Additional sollte aber eher die UserID mit sich tragen. Deswegen habe ich das gezeigte Script gleich so abgeändert.

Und noch etwas zu deinem Schema/Design. Benenne die einzelnen ID Spalten immer mit den Tabellennamen davor. Also UserID, AdditonalID, AddressID, CityID und CountryID. Später wenn du mal über mehrere Tabellen joinen solltest, wirst du es dir selber danken. Denn man verliert dann fix den Überblick (IMHO).

Ebenfalls sollten Tabellen immer im Plural geschrieben werden, da eine Tabelle ja mehrere Daten enthält. Also Users, Adiitonals, Cities usw... Wobei die ID Spalte dann weiterhin im Singular benannt wird.

zur Additional: Ich dachte eigentlich, dass jeder User nur eine Additional hat. Aber jetzt wo ich drüber nachdenke könnte man dann die Daten auch direkt zu User schreiben

Kommt natürlich auf dein Programm drauf an. Nur wenn ich lese "Additonals", was ja Zusätze sind, denke ich mir das ein User halt mehrere Zusätze haben kann. Deswegen hab ich das so "umgewerkelt" 😃

@juetho:
Über das Thema "Schreibweise" habe ich mich mal mit einem DB Admin über Stunden hinweg (fast) gestritten. Im Endeffekt ist es Geschmackssache. Ich vergleiche das immer dann gegen den Code. Wenn ich mehrere User habe, heißt die Klasse ja auch nicht User, sondern z.B. UserCollection. Ich sehe also sofort, das darin mehrere User enthalten sind.

Ich stehe ein wenig auf dem Schlauch

DECLARE @AdditionalID INT
				
SET @AdditionalID = INSERT INTO ADDITIONALS(Email, Website, Entrydate, Image, Phone0, Phone1, Cellphone)
					OUTPUT INSERTED.ADDITIONALSID
					VALUES('rolf.fletscher@gmail.com', 'www.rolf-fletscher.de', null, null, null, null, null);

INSERT INTO USERS(Forname, Surname, Birthday, Gender, [AdditionalID])
VALUES('Rolf', 'Fletscher', null, 'm', @AdditionalID)

Das Funktioniert nicht. Fehler ist "Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'INSERT'." Habe ich irgendwas vergessen?

Hallo BenediktFu,
so wie ich deinen Code verstehe ist die Id von Additionals eine Identity Spalte und du willst die zuletzt eingefügte Id verwenden um sie in der Tabelle users einzufügen.

Die zuletzt eingefügte ID kannst du mit SCOPE_IDENTITY() ermitteln.

Bsp:

DECLARE @AdditionalID INT
				
 INSERT INTO ADDITIONALS(Email, Website, Entrydate, Image, Phone0, Phone1, Cellphone)
					VALUES('rolf.fletscher@gmail.com', 'www.rolf-fletscher.de', null, null, null, null, null);

Set @AdditionalID = Scope_Identity()

INSERT INTO USERS(Forname, Surname, Birthday, Gender, [AdditionalID])
VALUES('Rolf', 'Fletscher', null, 'm', @AdditionalID)

Sebastian

Hallo Benedikt

Ich glaube was du suchst ist zum einen die Weiterverwendung der IDs (was Khalid und Sebastian schon gezeigt haben) zum anderen aber eine Möglichkeit sicherzustellen, dass entweder alles oder nix geschrieben wird. Also kein ungültiger Zustand in der Datenbank entsteht. Hierfür musst du eine Transaktion verwenden.

Wenn du die einzelnen INSERT Statements vom Client abschickst einfach mit Connection.BeginTransaction() und Transaction.Commit()/-.Rollback() arbeiten.

Wenn du mit einer Stored Procedure arbeitest, dann TRY-CATCH und BEGIN/COMMIT/ROLLBACK TRANSACTION.

Grüße
Flo

Hallo Benedikt

Ich glaube was du suchst ist zum einen die Weiterverwendung der IDs (was Khalid und Sebastian schon gezeigt haben) zum anderen aber eine Möglichkeit sicherzustellen, dass entweder alles oder nix geschrieben wird. Also kein ungültiger Zustand in der Datenbank entsteht. Hierfür musst du eine Transaktion verwenden.

Wenn du die einzelnen INSERT Statements vom Client abschickst einfach mit Connection.BeginTransaction() und Transaction.Commit()/-.Rollback() arbeiten.

Wenn du mit einer Stored Procedure arbeitest, dann TRY-CATCH und BEGIN/COMMIT/ROLLBACK TRANSACTION.

Grüße
Flo

Hallo. Ja das hört sich genauso an wie ich das brauche =) Jetzt hatte ich aber mit Transaktionen noch nie etwas zu tun. Hast du mir einen Link oder vielleicht Pseudo Code wie das ganze aussehen könnte?

Danke. Benedikt

Wenn über .NET dann einfach:

using (SqlConnection cn = new SqlConnection("mycn"))
{
   cn.Open();

   using (SqlTransaction tran = cn.BeginTransaction())
   {

      // Hier alle insert statements


      tran.Commit();

   } // Wenn die Transaktion nicht mit Commit() abgeschlossen wurde
     // führt das implizite Dispose() hier zu einem Rollback() aller Aktionen.

}

Grüße
Flo

Wenn über .NET dann einfach:

  
using (SqlConnection cn = new SqlConnection("mycn"))  
{  
   cn.Open();  
  
   using (SqlTransaction tran = cn.BeginTransaction())  
   {  
  
      // Hier alle insert statements  
  
  
      tran.Commit();  
  
   } // Wenn die Transaktion nicht mit Commit() abgeschlossen wurde  
     // führt das implizite Dispose() hier zu einem Rollback() aller Aktionen.  
  
}  
  

Grüße
Flo

Super vielen Dank !!! Werde ich nachher gleich ausprobieren.

Benedikt

@juetho:
Ich vergleiche das immer dann gegen den Code. Wenn ich mehrere User habe, heißt die Klasse ja auch nicht User, sondern z.B. UserCollection. Ich sehe also sofort, das darin mehrere User enthalten sind.

Ja genau, und deshalb muß die Tabelle User heißen, und nicht Users. Denn es heißt UserCollection, und nicht UsersCollection.
Jdfs ein typisiertes Dataset würde aus Users und Additionals sowas komisches an Code generieren wie:

UsersRow rwUser = myDataset.Users[2];
AdditionalsRow rwAdd = rwUser.GetAdditionalsRows()[1];        //doppelte plural-flexierung

Hi %

Ob die Tabellen jetzt "User", "Users", "Tbl_User", "Obj_User" oder "Nasenbär" heißen ist doch eigentlich egal. Man kann die Namen im Client (auch bei DataSets) ja eh noch anpassen.

Khalid's Einwand, bei Tabellen die Plural-Form zu verwenden, ist ein durchaus verbreitetes Design-Pattern um Tabellen wie "Order", "Group" oder eben "User" zu vermeiden. Vor Datumsspalten wie "From" und "To" schützt dass in letzter Instanz aber nicht.

Ich bin aber der Meinung, dass SQL Schlüssel-Wörter nicht verwendet werden sollten. Nur weil's in C# schlicht nicht möglich ist eine Klasse "class", "for" oder "special-case" zu nennen heißt nicht dass das in SQL besser ist.

Grüße
Flo

Wie gesagt: Streitthema 🙂

@Florian

public class @class
{

}

@Erfinder
Danke, wieder ein Grund weniger für Designer 😉

@Florian ...

...

Hallo,
ich bins nochmal. Und zwar habe ich noch eine Frage zwecks Fehlerbehebung bzw. Fehlervermeidung.

Ich möchte bevor ein Datensatz hinzugefügt wird natürlich prüfen ob teile oder der gesamte Datensatz schon besteht. Kann ich das mit IF EXISTS in SQL lösen? Oder soll ich das Codemäßig lösen indem ich mir zum Beispiel eine Liste aller Namen geben lasse und dann prüfe ob dieser schon vorhanden ist?

Was wäre hier effizienter bzw. "Zeitsparender"?

Danke. Benedikt

Hallo Benedikt

Naja, eigentlich sollte deine Anwendung wissen was zu Updaten und was zu Inserten ist. Schließlich haben die bestehenden Daten doch einen Id-Wert, oder?

Ansonsten ist IF EXISTS nicht 100% sicher, auch eine Client-Abfrage macht das nicht besser. Selbst WHERE NOT EXISTS(...) ist nicht 100%. Der einige korrekte Weg über SQL ist TRY-CATCH.

Der korrektest Weg bleibt aber dass der Client weiß was er tun soll.

CREATE TABLE [
] ([.] INT, [
] INT, [ ]]
] INT,"""" INT)

INSERT INTO [
] SELECT 1, 2, 3, 4

SELECT []]].[
], [,].[ ]]
] [[], [,].["],[[[
].[
],[,].[.]
FROM [
] []]], [
] [[[
],[
] ","

DROP TABLE [
]

Grüße
Flo

Hallo Benedikt

Naja, eigentlich sollte deine Anwendung wissen was zu Updaten und was zu Inserten ist. Schließlich haben die bestehenden Daten doch einen Id-Wert, oder?

Ansonsten ist IF EXISTS nicht 100% sicher, auch eine Client-Abfrage macht das nicht besser. Selbst WHERE NOT EXISTS(...) ist nicht 100%. Der einige korrekte Weg über SQL ist TRY-CATCH.

Der korrektest Weg bleibt aber dass der Client weiß was er tun soll.

Grüße
Flo

Hallo,
sorry verstehe ich jetzt nicht 100%ig 🤔 Es kann ja sein, dass ich zum Beispiel Bei CITYS Konstanz nochmal vorkommt. Jetzt soll er Konstanz natürlich nicht ein zweites mal in die Tabelle eintragen.

Vielleicht verstehe ich gerade nur nicht was du mit Client meinst und wie er wissen muss was er tun soll =)

Vielleicht erklärst du es einem auf dem SChlauch stehenden Benedikt nochmal für Dummies 👅

Benedikt

Hallo

Okay, du hast also einen eindeutigen Business-Key und die Stammdaten werden ggf. von unterschiedlichen Stellen importiert. Das war mir nicht klar.

Dann ist das einfachste und sicherste bei SQL Server 2008 die MERGE Funktion. Hier ein Beispiel (unten):
Performance Vergleich Abfragen mit LINQ

Grüße
Flo

Mir fallen zu dem Thema noch Unique-Constraints und evtl. auch noch der MERGE Befehl von T-SQL wenn es sich um den SQL Server 2008 Handelt.

Sebastian