SQL Insert avec des données provenant de plusieurs tables

J'ai quatre tableaux: les Messages, MessageCategory, MessageStatus et MessageLevel.

MessageCategory, MessageStatus et MessageLevel tout juste trois domaines: Identité (clé primaire), le Code et la Description. Les Messages se réfère à ces trois domaines et a quelques autres champs de données, y compris l'Identité (clé primaire) MessageText et de l'Ordre. L'Identité champs auto-incrémentés champs.

J'ai maintenant besoin d'écrire un script SQL pour ajouter des données par défaut à tous les quatre tableaux. Le problème c'est que je dois créer un script qui sera envoyé à un client, qui pourra ensuite exécuter ce script. Je ne peux pas écrire un peu plus intelligente de code à faire la mise à jour. Et tandis que les trois tableaux sont de simples instructions insert, c'est la table des Messages qui me cause quelques autres maux de tête.

Je ne peut pas supprimer les indices et je ne peut pas supposer qu'il commence à compter à 1 pour les clés primaires.

Alors, comme un exemple, avec ses quelques données:

INSERT INTO MessageCategory (Code) Values ('Cat01');
INSERT INTO MessageStatus (Code) Values ('Status01');
INSERT INTO MessageLevel (Code) Values ('Level01');

Et les messages aurait besoin de quelque chose comme ceci:

INSERT INTO Messages(Category, Status, Level, MessageText, Order) 
VALUES(
  (SELECT Identity from MessageCategory where Code='Cat01'), 
  (SELECT Identity from MessageStatus where Code='Status01'), 
  (SELECT Identity from MessageLevel where Code='Level01'), 
  'Just some message', 
  1
);

Qui ne fonctionnent pas, bien. Alors, quelle est l'astuce pour le faire fonctionner? (En gardant le code lisible trop...)

Malheureusement, je n'ai pas accès à l'autre de la base de données. Je peux le tester, mais une fois qu'il semble fonctionner, c'est juste une question de envoyer-et-prier-il-d'œuvres...

OriginalL'auteur Wim ten Brink | 2009-10-19

  1. 6
    INSERT INTO Messages 
  (Category, Status, Level, MessageText, [Order]) 
SELECT
  (SELECT TOP 1 [Identity] from MessageCategory where Code='Cat01')  AS Category,
  (SELECT TOP 1 [Identity] from MessageStatus where Code='Status01') AS Status,
  (SELECT TOP 1 [Identity] from MessageLevel where Code='Level01')   AS Level,
  (SELECT 'Just some message')   AS MessageText, 
  (SELECT 1)                     AS [Order]

    Ci-dessus serait de travailler pour SQL Server. Notez que les deux Identity et Order sont réservées T-SQL mots-clés et ne doit pas être utilisé pour les noms de colonne. Notez également que les sous-requêtes ne doivent pas retourner plus d'une ligne pour que cela fonctionne, pour s'assurer que j'ai compris TOP 1 consolidés.

    La prochaine chose à noter est que l'alias de colonne (AS Category etc.) ne sont pas strictement nécessaires. Leur ordre est ce qui compte. Je voudrais inclure pour des raisons de lisibilité, en particulier lorsque la liste de sélection est plus longue.

    J'ai tendance à toujours utiliser les crochets [] autour de toutes les tables et les noms de champ dans mes scripts SQL. Qui résout les conflits possibles. J'ai tendance à utiliser plus de mots réservés en tant que champ les noms de table et donc ajouter des parenthèses est un système automatisé d'action pour moi. 🙂
    Vous devez donc vous en avez fait qu'à votre exemple de code. ainsi. ;-)</information>
    Ouais, je m'attendais à ces crochets ne pas confondre un peu et j'ai donc supprimé. 🙂
    Notez que cela ne fonctionne que si le Code est unique dans votre tableau. Je suppose qu'il est dans ce cas, mais...
    J'ai gardé le haut-1, juste au cas où, de toute façon. 🙂 Il y a une clé unique sur les champs de sorte qu'il ne devrait pas être possible, mais encore... Il montre l'essence de la requête d'insertion.

    OriginalL'auteur Tomalak

  2. 4

    Si c'est un simple script, stocker vos identités dans les variables:

    declare MessageCategoryID int;
declare MessageStatusID int;
declare MessageLevel int;
INSERT INTO MessageCategory (Code) Values ('Cat01');
set @MessageCategoryID=scope_identity();
INSERT INTO MessageStatus (Code) Values ('Status01');
set @MessageStatudID=scope_identity();
INSERT INTO MessageLevel (Code) Values ('Level01');
set @MessageLevelID=scope_identity();

INSERT INTO Messages(Category, Status, Level, MessageText, Order) 
    VALUES(
        @MessageCAtegoryID,
        @MessageStatusID,
        @MessageLevelID,
        'Just some message',
        1);
    C'est un simple script, mais il y aura plusieurs dizaines d'enregistrements dans la table des Messages.
    Et ne jamais utiliser @@identity utilisation scope_identity à la place. @@identity peut causer de graves dat problèmes d'intégrité si un déclencheur est ajouté à votre table.
    Jamais utilisé scope_identity(), mais à partir de maintenant. Merci pour l'astuce!

    OriginalL'auteur Kendrick