Discussion :

[dymezac]

Bonjour,

je soliccite votre aide pour une requête qui me pose problème :

P.S. :
- (je travaille avec du C# et Sql Serveur 2005, pour le code ci dessous je simplifie pas mal)
- COALESCE sert à remplacer les null par des 0

je voudrais éviter de faire deux requêtes (avec transaction serializable) :
1) récupérer le dernier id de ma table +1
2) insérer mon enregistrement avec l'id que j'ai récupérer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nouvelID = "SELECT (COALESCE(MAX(maTable_id),0)+1) FROM maTable;"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO maTable (maTable_id, maTable_lib) VALUES (nouvelID, 'du texte');

en une seule :
1) insérer mon enregistrement avec mon nouvel Id en même temps :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO maTable (maTable_id, maTable_lib) 
VALUES ((SELECT (COALESCE(MAX(maTable_id),0)+1) FROM maTable), 'du texte');

Mais je n'y arrive pas..
Sql Server me dit que je ne peux pas faire de sous requête dans ce contexte et que seules les expressions scalaires sont permises.

Alors j'ai essayé de mettre seulement COALESCE(MAX(maTable_id)) mais là il me dit que les noms de colonnes ne sont pas autorisées dans ce contexte..

Donc j'ai l'impression que ce n'est pas possible d'insérer un nouvel enregistrement en sélectionnant le (max des ID +1) dans la même requête..

Est ce que je ne peux vraiment pas le faire par aucun moyen et est ce que je suis vraiment obligé de faire deux requêtes pour insérer mes données ?
si oui je trouve ça domage mais s'il le faut il le faut..

d'avance merci beaucoup.

[SQLpro]

Lisez ceci :
http://sqlpro.developpez.com/cours/clefs/

Cela vous évitera au passage d'énormes bétises.

Intéressez vous aussi à la propriété IDENTITY que peut revêtir une colonne.

A +

[dymezac]

bonjour SqlPro et merci pour votre réponse,

en fait je dois travailler avec des tables dont les clés primaires ne sont pas auto-incrémentés et je dois le faire par moi même..

Et j'ai déjà tout lu sur le lien que vous m'avez donner (en passant, cet article m'a beaucoup appris)

Dans la mesure ou mes transactions sont 'SERIALIZABLE' je pense (? à tort ??) que ça ne pose pas de problème si je fais un Select MAX pour avoir un nouvel identifiant de clé primaire..

finalement, pour le problème que j'ai posé sur ce post, j'ai été obligé de passer par deux requêtes (en faisant mon select max)..

à moins que vous connaissiez un moyen de faire ces deux requêtes en une seule ?

encore merci !

[WOLO Laurent]

Vous pouvez créer une fonction UDF et la jouer dans votre commande insert et ca marche.
Merci.

[dymezac]

d'accord merci beaucoup, je vais me renseigner tout de suite sur les UDF (je ne connaissais pas). merci

[SQLpro]

Vous êtes obligés de faire deux requêtes et en mode SERIALIZABLE... Mais savez vous ce qui se passe dans le mode SERIALIZABLE ? Tout simplement un verrou exclusif est posé sur les ressources traitées. Aucun autre process ne peut donc ni lire ni écrire sur les ressources vérrouillées. Or en demandant une requête SELECT MAX(...) ... les ressources à considérer sont la table entière !!!
Au cours du développement au début de vos test, lorsqu'il y a peu d'utilisateurs et peu de données, cette méthode va donner de bons résultats dans des temps acceptable. Mais réfléchissez 30 secondes en essayant d'analyser ce qui va se passer lorsque vous aurez une table de 50 000 lignes et 200 utilisateurs simultanés... vous aurez des temps de réponse catastrophique, du aux bloquages imposés par les verrous et très certainement des interbloquages (deadlock, étreinte fatale, verrous mortel...). Bref si vous voulez livrer une application des plus pourries, continuez comme cela ! Mieux, utilisez des fichiers COBOL ce sera plus propre...

De plus je ne comprend pas votre souhait de ne pas utiliser l'auto incrément de SQL Server. Il est normalisé depuis la version SQL:2003 de la norme ISO de SQL. Il peut aussi être débranché pour des insertion avec des valeurs de clefs forcées. Je ne voit donc aucune incompatibilité pour l'utiliser dans toutes les circonstances...

Seul un manque de culture reflète votre incompréhension...

A +

[dymezac]

et bien merci vous nous avez convaincu..
nous allons utiliser les auto increment et les transactions 'read commited'
votre aide a été bien utile.. merci

[dymezac]

Bonjour SQLPro et WOLO Laurent,

je reviens vous poser une dernière question :

j'ai modifié la base de données et j'ai mis l'auto-incrément identity(1,1) sur toutes les clés primaires.

maintenant dans mon code ASP.NET, pour récupérer la valeur de la clé qui a été générée par l'autoincrément, puis je faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT  INTO Ma_requete; SELECT @@IDENTITY;

dans une seule et même commande sql (SqlCommand en ASP.NET)

est ce que je suis sur que même si je suis en mode transaction READ COMITTED, aucune requete concurrente ne va s'intercaler entre mon INSERT et mon SELECT @@IDENTITY ??? (du fait que je le fais dans une même commande sqlCommand sous asp.net)

si oui, mon problème est résolu.. mais sinon il va me falloir trouvé une solution.

d'avance merci,
et surtout joyeuses fêtes et bonne année

[SQLpro]

Non, vous ne pouvez pas enchâiner 2 requêtes en une seule commande sauf dans le cadre d'un batch.

Une solution plus astucieuse, plus intelligente et très performante consiste à utiliser une procédure stockée.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE PROCEDURE P_INSERT_RESULT_IDENTITY @COL1 ..., @COL2 ...
AS
 
INSERT INTO ... VALUES (@COL1, @COL2 ... )
 
SELECT @@IDENTITY AS LAST_ID

Pour utiliser une procédure utilisez un objet ADO de type Procédure stockée et passez lui les paramètres. Lancez la proc avec un OPEN et lisez le résultat.

Vous pouvez aussi utiliser un paramètre output et le lire dans les paramètres de la proc stock.

2eme exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE PROCEDURE P_INSERT_RESULT_IDENTITY @COL1 ..., @COL2 ..., @IDOUT INTEGER OUTPUT
AS
 
INSERT INTO ... VALUES (@COL1, @COL2 ... )
 
SET @IDOUT = @@IDENTITY

A +

[dymezac]

Bonjour SqlPro,

Avant tout, bonne année et mes meilleurs voeux de réussite et de lignes de code pour cette nouvelle année 2007

Merci pour votre réponse.. je suis actuellement en vacances sans internet mais dès que je reprends le travail je vais suivre vos instructions. J'imaginais pouvoir m'en sortir sans les procédures stockées (nouveau pour moi) mais finalement il va bien falloir. Je vais faire ça dès mardi à la rentrée.

Par contre il apparait que je vais devoir créer une procédure stockée par table de la BDD ?

A+ et vraiment merci pour votre présence sur ce forum !

[dymezac]

rebonjour,

tout marche avec les procédures stockées. par contre j'utilise un scope_identity() dans ma procédure. encore merci et bonne année.

a+

[dymezac]

Bonjour SQLPro,

Non, vous ne pouvez pas enchâiner 2 requêtes en une seule commande sauf dans le cadre d'un batch.

Je reviens sur mon problème car finalement nous ne souhaitons pas (si c'est possible) créer autant de procédure stockée qu'il y a de table dans notre base de données.

Donc en cherchant ce matin je me suis aperçu que je peux enchaîner 2 requêtes sql en une seule commande grâce aux "lots d'instructions" :

ci-dessous une citation de la documentation msn à ce sujet :

Un lot est un groupe d'instructions Transact-SQL qu'une application envoie simultanément à SQL Server 2005 en vue de leur exécution. SQL Server compile les instructions d'un lot en une unité exécutable unique, appelée « plan d'exécution ». Les instructions appartenant à un plan d'exécution sont ensuite exécutées simultanément.

http://msdn2.microsoft.com/fr-fr/library/ms175502.aspx



Or, d'après la documentation MSN, un SCOPE_IDENTITY() renvoie l'id généré dans une étendue (et un lot est une étendue d'aprés la définition d'une étendue) :

doc msn ci dessous :

SCOPE_IDENTITY (Transact-SQL)

Renvoie la dernière valeur d'identité insérée dans une colonne d'identité dans la même étendue. Une étendue est un module : procédure stockée, déclencheur, fonction ou lot. Par conséquent, deux instructions sont dans la même étendue si elles se trouvent dans la même procédure stockée ou fonction, ou dans le même lot.

Suis je sur une mauvaise piste, ou ai je la mon ultime solution ?

Si j'ai la bonne solution, je pourrai donc faire depuis ma page asp.net mes 2 requêtes en une seule commande :

Code C# :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
string strRequete = "INSERT INTO [...]; SELECT @@IDENTITY;";
 
SqlCommand commande = new SqlCommand(strRequete);
 
// .. puis je récupère le résultat de ma commande sqlCommande 
// qui me retournera l'id généré par mon INSERT

voir aussi :
http://msdn2.microsoft.com/fr-fr/library/ms189115.aspx


.

[dymezac]

P.S. : en deuxième solution j'ai lu aussi que @@Identity renvoie l'id généré dans la session active.. Or si une session active est bien la connection de l'utilisateur (si j'ai bien compris), alors j'avais aussi cette solution, sachant que dans mon appli asp.net, un utilisateur (une session) ne peut pas avoir deux connections à la bdd (une connection par utilisateur).