Problème d'insertion sur table avec une unique colonne identity

**Kropernic** · 02/10/2013, 10h23

Bonjour,

Comment insérer plusieurs lignes avec un seul ordre INSERT dans une table comportant juste une colonne identity ?

Pour insérer une ligne, c'est facile :

Code :

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

INSERT INTO LA_TABLE DEFAULT VALUES

Mais je voudrais pouvoir faire des insertions ensemblistes et non itératives...

Un exemple concret avec le DDL suivant qui comprend la table posant problème (T_NATIONALITE_NAT), une table qui la référence (T_NATIONALITE_LNG_NAL) et une vue qui fait la jointure des deux (V_NATIONALITE_VNT) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE TABLE [dbo].[T_NATIONALITE_NAT](
    [NAT_ID] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [NAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE TABLE [dbo].[T_NATIONALITE_LNG_NAL](
    [NAT_ID] [int] NOT NULL,
    [LNG_ID] [tinyint] NOT NULL,
    [NAL_LIB] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [NAT_ID] ASC,
    [LNG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[T_NATIONALITE_LNG_NAL]  WITH CHECK ADD  CONSTRAINT [FK_T_NATIONALITE_LNG_NAL_LNG_ID] FOREIGN KEY([LNG_ID])
REFERENCES [dbo].[T_LANGUAGE_LNG] ([LNG_ID])
GO
 
ALTER TABLE [dbo].[T_NATIONALITE_LNG_NAL] CHECK CONSTRAINT [FK_T_NATIONALITE_LNG_NAL_LNG_ID]
GO
 
ALTER TABLE [dbo].[T_NATIONALITE_LNG_NAL]  WITH CHECK ADD  CONSTRAINT [FK_T_NATIONALITE_LNG_NAL_NAT_ID] FOREIGN KEY([NAT_ID])
REFERENCES [dbo].[T_NATIONALITE_NAT] ([NAT_ID])
GO
 
ALTER TABLE [dbo].[T_NATIONALITE_LNG_NAL] CHECK CONSTRAINT [FK_T_NATIONALITE_LNG_NAL_NAT_ID]
GO
 
CREATE VIEW [dbo].[V_NATIONALITE_VNT]
AS
SELECT
        NAT.NAT_ID,
        LNG_ID,
        NAL_LIB
FROM
        DBO.T_NATIONALITE_NAT NAT
            INNER JOIN DBO.T_NATIONALITE_LNG_NAL NAL
                ON    NAT.NAT_ID = NAL.NAT_ID
GO

L'idée est donc de placer un trigger instead of insert et update sur la vue pour permettre d'effectuer les insertions et mises à jour correctement (qui à priori ne devraient pas être nombreuses, je vous l'accorde).

Mais je bloque sur l'instruction à utiliser pour insérer de manière ensembliste dans la table dont le suffixe est NAT...

**mikedavem** · 02/10/2013, 15h26

Quelle version de SQL Server utilises-tu ?

De plus quel est l'intérêt du point de vue sémantique dans ton cas d'avoir une table avec une seule colonne identity ?

++

**Kropernic** · 02/10/2013, 15h31

Envoyé par mikedavem

Quelle version de SQL Server utilises-tu ?

2008R2 (c'est dans le titre

)

Envoyé par mikedavem

De plus quel est l'intérêt du point de vue sémantique dans ton cas d'avoir une table avec une seule colonne identity ?

En cas d'évolution du modèle, il est alors possible d'ajouter des propriétés à chaque nationalité sans avoir à les répéter pour chaque langue.

Par exemple, j'ai déjà envisagé d'ajouter une colonne faisant référence à la table T_PAYS_PAY pour indiquer le pays correspondant à cette nationalité.

**mikedavem** · 02/10/2013, 15h42

2008R2 (c'est dans le titre )

Je ne m'y habitue pas aux versions avant les titres

En cas d'évolution du modèle, il est alors possible d'ajouter des propriétés à chaque nationalité sans avoir à les répéter pour chaque langue.
Par exemple, j'ai déjà envisagé d'ajouter une colonne faisant référence à la table T_PAYS_PAY pour indiquer le pays correspondant à cette nationalité.

Tu la mettrais où cette nouvelle colonne ?

++

**Kropernic** · 02/10/2013, 15h50

Bah dans la table T_NATIONALITE_NAT...

En faisant quelque chose du genre :

Code :

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

1
2
3
4
5
6
7
8
9
 
ALTER TABLE dbo.T_NATIONALITE_NAT
ADD PAY_ID SMALLINT NOT NULL;
GO
 
ALTER TABLE dbo.T_NATIONALITE_NAT
ADD CONSTRAINT FK_T_NATIONALITE_NAT_PAY_ID FOREIGN KEY (PAY_ID)
    REFERENCES dbo.T_PAYS_PAY (PAY_ID);
GO

Je ne comprends pas bien le pourquoi de ces questions. Surtout venant quelqu'un que je sais très expérimenté. Je pensais ces pratiques courantes et normales....

**mikedavem** · 02/10/2013, 16h00

Ok donc pourquoi ne pas dans ce cas ajouter directement une colonne "dummy" ou directement ta colonne concernant le pays car cela t'évitera de modifier par la suite ton code d'insertion.

Le problème c'est qu'avec une seule colonne identity il faut ruser un peu et utiliser par exemple un MERGE puisque tu es sur 2008 R2 .. quelque chose du genre (à tester avec le trigger) :

Code :

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

1
2
3
4
5
6
7
MERGE INTO dbo.T_NATIONALITE_NAT
USING (SELECT col ... 
          FROM INSERTED)
ON 1 = 0
WHEN NOT MATCHED 
 THEN INSERT DEFAULT VALUES
OUTPUT INSERTED.id .....

.. ce qui rend le code un peu lourd ... alors que si tu as déjà une autre colonne le code sera plus simple :

Code :

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

1
2
3
INSERT dbo.T_NATIONALITE_NAT (dummy / pays_id)
SELECT col ...
FROM inserted

++

**Kropernic** · 02/10/2013, 16h11

Arf... J'avais déjà regardé du côté de l'instruction MERGE mais il y a tellement de paramètres décrits sur la MSDN que j'avoue avoir été découragé avant de commencer la lecture...

Pour la solution avec la "fausse" colonne, on parle donc bien d'une colonne qui sera nullable et dans laquelle jamais rien ne serait insérée ?

C'est un peu moche non ? Mais bon, c'est vrai que ça pourrait aider...

Remarque, dans mon cas, je peux utiliser ton truc sans ajouter de "fausse" colonne. J'ai en fait ce que j'appelle des colonnes de tracking dans presque chaque table. Celle-ci normalement ne devrait pas en contenir mais je pourrais faire une exception. Il s'agit de colonne stockant quand et par qui la ligne a été insérée ainsi que quand et par qui elle a été modifiée.

Par curiosité, quelle aurait été la solution en 2012 ?

**mikedavem** · 02/10/2013, 16h20

Pour la solution avec la "fausse" colonne, on parle donc bien d'une colonne qui sera nullable et dans laquelle jamais rien ne serait insérée ?

C'est un peu moche non ? Mais bon, c'est vrai que ça pourrait aider...

Oui une colonne avec possibilités d'absences de valeurs dans laquelle tu pourrais insérer des absences de valeur

Effectivement ça fait un peu "moche" mais ca à mon avis ca pourrait valoir le coup dans ton texte. Maintenant effectivement si tu as des colonnes de tracking .. à voir si tu peux t'en servir comme artifice

Par curiosité, quelle aurait été la solution en 2012 ?

En 2012 tu pourrais utiliser les séquences et la pas de souci ..

Bon je sais que parfois mes questions peuvent paraître bête mais elles sont vraiment là afin de proposer une bonne solution. Sans réfléchir c'est que j'aurai pu te dire utilises MERGE et on en parle plus mais je pense qu'il y a plus simple comme démarche

++

**Kropernic** · 03/10/2013, 15h22

C'est de nouveau moi. En fait, je suis encore perdu sur ce sujet.

J'ai donc ajouté une collonne nullable qui ne servira à rien nommée VOID et de type bit.

J'ai alors commencé la rédaction du trigger instead of insert mais je câle...

Comment déterminer le nombre de ligne à ajouter dans la table NAT ?
La vue se compose des colonnes NAT_ID (int identity), LNG_ID (tinyint) et NAL_LIB (varchar(50).
Vu que NAT_ID est de type identity, aucune valeur n'est donnée en input lors de l'insertion.
Du coup, comme connaître les lignes que j'insère qui sont des traductions l'une de l'autre (et qui doivent donc à la fin partager la même valeur pour NAT_ID) ???

**mikedavem** · 03/10/2013, 20h42

Quelque chose du genre pour un trigger instead of insert ... la logique est peut être à modifier en fonction de ton contexte

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
CREATE TRIGGER TR_TEST
ON [dbo].[V_NATIONALITE_VNT]
INSTEAD OF INSERT
AS
 
DECLARE @t_inserted_view TABLE
(
	num INT IDENTITY(1,1) NOT NULL,
	lng_id TINYINT NOT NULL,
	nal_lib VARCHAR(50)
);
 
DECLARE @t_inserted_id TABLE
(
	num INT IDENTITY(1,1) NOT NULL,
	id INT NOT NULL
);
 
-- insert new values from insert
INSERT @t_inserted_view (lng_id, nal_lib, dummy)
SELECT 
	LNG_ID,
	NAL_LIB
FROM inserted;
 
 
-- Get new identity values into @t_inserted_id 
INSERT DBO.T_NATIONALITE_NAT (dummy)
OUTPUT INSERTED.NAT_ID
INTO @t_inserted_id (id)
SELECT NULL FROM inserted;
 
-- Insert of lng_id and nav_lib information in 
-- DBO.T_NATIONALITE_LNG_NAL table
INSERT INTO DBO.T_NATIONALITE_LNG_NAL 
SELECT 
	i.id,
	v.lng_id,
	v.nal_lib
FROM @t_inserted_view AS v
	JOIN @t_inserted_id AS i
		ON v.num = i. num;
GO

++

**mikedavem** · 03/10/2013, 20h59

Ah désolé je n'avais pas vu ta dernière phrase et effectivement dans ce cas il va falloir changer un peu ce que j'ai fait.

++

**mikedavem** · 03/10/2013, 21h14

Enfin à y penser à chaud comme cela, avec la méthode actuelle il n'y aura aucun moyen d'identifier 2 traductions d'une même nationalité lors d'une première insertion.

Tu peux par exemple soit faire une première insertion avec une ligne de traduction et jouer avec un trigger instead of update pour ajouter les autres .... à voir à froid ...

++

**Kropernic** · 04/10/2013, 08h16

Ah ouf, ça me rassure !!! J'suis pas complètement con

.

Bon bin je vais laisser tomber. Après tout, ce n'est pas le genre de table qui devrait subir des modifications... J'vais donc faire une vue pour facilité la sélection mais tant pis pour les insert et update...

Merci de t'être pencher sur ce cas.

Problème d'insertion sur table avec une unique colonne identity [2008R2]

Développement SQL Server

Discussions similaires

Partager

Partager