[2005]Insertion par lot et héritage

**Kropernic** · 05/11/2012, 15h48

Bonjour,

Je suis confronté à une problématique pour la première et j'ai du mal à voir par quel bout l'aborder.

Suite à une action de l'utilisateur dans une application, je suis amené à insérer un lot de nouvelles lignes dans une table.

Jusque là, si j'ai bien compris, histoire de faire x appels à la même procédure stockée où x est le nombre de lignes à insérer, je dois créer un nouveau "user-defined table type" et l'utiliser comme paramètre de la procédure stockée.

Jusque là, pas de souci je pense (je n'ai pas encore testé mais cela me semble clair).

Le problème est que la table dans laquelle je dois faire les insertions est hérite d'une autre table. Pour insérer une ligne, pas de souci, je fais mon insertion dans la table parente et je récupère l'identifiant avec SCOPE_IDENTITY() pour savoir quoi mettre dans la table fille.

Mais si j'insère par lot, comment je récupère les identifiants ?

Voici le DDL des deux tables en question.
La table parente

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
USE [GIFT_MANAGEMENT]
GO
 
/****** Object:  Table [dbo].[T_GIFT_GFT]    Script Date: 11/05/2012 14:46:54 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[T_GIFT_GFT](
    [GFT_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_T_GIFT_GFT] PRIMARY KEY CLUSTERED 
(
    [GFT_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

La table fille

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
USE [GIFT_MANAGEMENT]
GO
 
/****** Object:  Table [dbo].[T_CARD_CRD]    Script Date: 11/05/2012 14:47:26 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[T_CARD_CRD](
    [GFT_ID] [int] NOT NULL,
    [CRD_PREFIX] [tinyint] NOT NULL,
    [TCA_ID] [smallint] NOT NULL,
    [CRD_SERIAL] [int] NOT NULL,
    [CRD_RECHARGEABLE] [bit] NOT NULL,
 CONSTRAINT [PK_T_CARD_CRD] PRIMARY KEY CLUSTERED 
(
    [GFT_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
 
ALTER TABLE [dbo].[T_CARD_CRD]  WITH CHECK ADD  CONSTRAINT [FK_T_CARD_CRD_T_GIFT_GFT] FOREIGN KEY([GFT_ID])
REFERENCES [dbo].[T_GIFT_GFT] ([GFT_ID])
GO
 
ALTER TABLE [dbo].[T_CARD_CRD] CHECK CONSTRAINT [FK_T_CARD_CRD_T_GIFT_GFT]
GO
 
ALTER TABLE [dbo].[T_CARD_CRD]  WITH CHECK ADD  CONSTRAINT [FK_T_CARD_CRD_T_TYPE_CARD_TCA] FOREIGN KEY([TCA_ID])
REFERENCES [dbo].[T_TYPE_CARD_TCA] ([TCA_ID])
GO
 
ALTER TABLE [dbo].[T_CARD_CRD] CHECK CONSTRAINT [FK_T_CARD_CRD_T_TYPE_CARD_TCA]
GO
 
ALTER TABLE [dbo].[T_CARD_CRD] ADD  CONSTRAINT [DF_T_CARD_CRD_CRD_PREFIX]  DEFAULT ((24)) FOR [CRD_PREFIX]
GO

S'il y a un point sur lequel je n'ai pas été clair, n'hésitez pas !

Merci d'avance.

EDIT :

Précision car j'ai pas l'impression que c'est très clair à la relecture.
J'aimerais arriver au final à une procédure de ce genre là :

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
 
CREATE PROCEDURE UP_CARD_LIST_INSERT
    @LIST    CARD_LIST
BEGIN
    SET NOCOUNT ON;
 
    --insertion de x ligne dans la table T_GIFT_GFT
    INSERT INTO DBO.T_GIFT_GFT DEFAULT VALUES; --comment spécifier le nombre de fois ?  je fais une simple boucle ou bien il y a un truc?
 
    INSERT INTO DBO.T_CARD_CRD(GFT_ID, CRD_PREFIX, TCA_ID, CRD_SERIAL, CRD_RECHARGEABLE)
    SELECT
            GFT_ID,--où récupérer cette donnée?
            '24',
            L.TCA_ID,
            L.CRD_SERIAL,
            L.CRD_RECHARGEABLE
    FROM
            @LIST;
 
END

**Kropernic** · 05/11/2012, 17h43

J'ai imaginé une solution mais je viens vous la soumettre car je n'en suis qu'à moitié convaincu.

Si avec un trigger after insert sur la table T_GIFT_GFT, j'insère les identifiants nouvellement créés dans une table de travail T_GIFT_TAMPON_GTT qui aurait comme colonnes GFT_ID (identifiant de la table gift) et GTT_USER qui contiendrait le username de l'utilisateur obtenu avec SYSTEM_USER.

De cette manière, je peux récupérer facilement tous les id's concerné par l'insertion de masse de la procédure non ?

Une fois mes insertions terminées, je nettoie cette table de travail des lignes faisant référence à l'utilisateur concerné.

Qu'en pensez-vous ?

**Kropernic** · 05/11/2012, 18h10

Une petite simulation de la solution que je viens décrire dans le message précédent

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
52
53
54
55
56
57
58
59
60
DECLARE @LIST TABLE    (
                    TCA_ID SMALLINT NOT NULL,
                    CRD_SERIAL INT NOT NULL,
                    CRD_RECHARGEABLE BIT NOT NULL
                    PRIMARY KEY (TCA_ID, CRD_SERIAL)
                    )
 
INSERT INTO @LIST VALUES (9, 20, 0);
INSERT INTO @LIST VALUES (9, 21, 0);
INSERT INTO @LIST VALUES (9, 22, 0);
INSERT INTO @LIST VALUES (9, 23, 0);
INSERT INTO @LIST VALUES (9, 24, 0);
INSERT INTO @LIST VALUES (9, 25, 0);
INSERT INTO @LIST VALUES (9, 26, 0);
INSERT INTO @LIST VALUES (22, 30, 0);
INSERT INTO @LIST VALUES (22, 31, 0);
INSERT INTO @LIST VALUES (22, 32, 0);
 
DECLARE @ITER INT;
DECLARE @COUNT INT;
SET @ITER = 0;
SELECT @COUNT = COUNT(*) FROM @LIST
 
WHILE @ITER < @COUNT
BEGIN
    INSERT INTO T_GIFT_GFT DEFAULT VALUES;
    SET @ITER = @ITER + 1
END;
 
WITH T1(GFT_ID, ROW_NUM)
AS(
    SELECT
            GFT_ID,
            ROW_NUMBER() OVER(ORDER BY GFT_ID) AS 'ROW_NUM'
    FROM    
            T_GIFT_TAMPON_GTT
    WHERE
            GTT_USER = SYSTEM_USER
),
T2(TCA_ID,CRD_SERIAL,CRD_RECHARGEABLE,ROW_NUM)
AS(
    SELECT
            TCA_ID,
            CRD_SERIAL,
            CRD_RECHARGEABLE,
            ROW_NUMBER() OVER(ORDER BY TCA_ID,CRD_SERIAL) AS 'ROW_NUM'
    FROM
            @LIST
)
 
SELECT
        T1.GFT_ID,
        '24' AS 'CRD_PREFIX',
        T2.TCA_ID,
        T2.CRD_SERIAL,
        T2.CRD_RECHARGEABLE
FROM        
        T1    
            INNER JOIN T2
                ON T1.ROW_NUM = T2.ROW_NUM

Qu'en pensez-vous ?

Y a-t-il un moyen d'éviter cette vilaine boucle itérative pour avoir quelque chose de purement ensembliste ?

P.S. : Il manque bien sûr la partie où je nettoie la table tampon.

**SQLDev** · 06/11/2012, 10h32

Bonjour Kropernic,

Regarde du côté de OUTPUT et INSERTED.

**Kropernic** · 06/11/2012, 10h42

Pourrais-tu préciser ?

Tu parles de variable de sortie et de la pseudo table inserted ?

Si oui, je vois bien les deux concepts mais je ne vois pas ce que tu veux que j'en fasse...

**SQLDev** · 06/11/2012, 12h25

Autant pour moi, j'étais resté sur le problème de récupération d'Id créés en masse, sans regarder comment ajouter plusieurs ligne avec la valeur par défaut.

A part ajouter une colonne dans la table parente qui peut contenir toujours NULL ce qui n'est pas terrible, je ne vois pas comment faire sans boucle.

**Kropernic** · 06/11/2012, 12h28

Ok bin je pense que je vais rester avec ça alors...

J'ai créé ma procédure stockée et je l'ai testée dans SSMS et ça fonctionne.

Je posterai le code complet de celle-ci d'ici quelques minutes.

Il y a probablement deux ou trois petites choses à améliorer.