Problème d'insertion ensembliste

Version imprimable

26/02/2016, 11h37
Kropernic

2 pièce(s) jointe(s)

Problème d'insertion ensembliste

Hello,

Je crois que j'ai le cerveau grillé car ce problème m'a l'air d'être quelque chose d'assez courant à faire et pourtant, je sèche totalement...

J'ai les tables suivantes :
Pièce jointe 202016

J'ai aussi, tiré d'une vieille DB (pas forcément normalisée), un historique, qui a été importé dans une table "temporaire" (c'est une vraie table mais elle sera supprimée dès l'importation dans les tables définitives réussie), avec lequel je voudrais peupler les tables dont les suffixes sont CRD, OCN et EMC.

Un peu d'explication...

Il s'agit d'une DB dont le but est de gérer des numéros de cartes de membre du personnel. La table terminant par EMP contient la liste du personnel actif et pensionné mais cette liste ne remonte que jusqu'à l'époque où nous avons changé de secrétariat social il y a 3 ans. Par contre, la vieille DB dont je tire l'historique n'était liée à rien. Du coup, j'ai TOUT dedans. La table OEM est donc là pour ça mais je ne m'en occupe pas pour le moment. Dans un premier temps, je veux uniquement peupler les tables avec le personnel actif.

Dans la table CRD, la colonne CRD_NUMBER est une colonne calculée qui donne le numéro de série de la carte. Pour les nouvelles cartes qui seront créées, c'est ce numéro calculé qui sera utilisé. Pour les anciennes, c'est celui de la table OCN qui reste valide. Toujours dans la table CRD, la colonne CRD_YEAR donne l'année de création de la carte.

Là où je sèche...

Je suis conscient que je pourrais créer un curseur et itérer sur les lignes de la table temporaire dont je parle plus haut pour créer, une à une, les lignes dans les autres tables.
Le souci c'est que j'aime les choses bien faite et que je cherche à travailler de manière ensembliste (comme il faudrait toujours le faire avec le t-sql).
Et je ne parviens pas à, lors de l'insertion dans la table T_CARD_CRD, produire un output qui me permet de relier chaque ligne insérée à la ligne correspondante de l'historique de manière à pouvoir faire les insertions correctes dans les autres tables.

Je suis pourtant persuadé que c'est plus ou moins trivial mais je dois avoir le cerveau grillé... Ca fait 2 jours que je sèche dessus.

Help please :oops:.

Merci d'avance à ceux qui auront pris la peine de lire ce message.
26/02/2016, 11h59
Sebwar

Hello !

A quoi ressemble la table "temporaire" de la vielle DB ?

À ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE [S_TEMP].[T_ACTIVE_EMPLOYEE_AEM]( [AEM_ID] [int] IDENTITY(1,1) NOT NULL, [AEM_LASTNAME] [varchar](100) NOT NULL, [AEM_FIRSTNAME] [varchar](100) NOT NULL, [AEM_GENDER] [char](1) NOT NULL, [AEM_LANGUAGE] [char](1) NOT NULL, [AEM_NATIONAL_REGISTRY] [char](11) NOT NULL, [AEM_DATE_IN] [date] NULL, [AEM_DATE_OUT] [date] NULL, [AEM_DONT_CARE] [char](1) NULL, [AEM_TYPE] [char](1) NULL, [AEM_CARD_NUMBER] [varchar](16) NULL, PRIMARY KEY CLUSTERED ( [AEM_ID] ASC ) ) ON [PRIMARY]
EDIT : Tant que j'y suis, j'en profite pour préciser que, dans l'ancienne DB, le numéro était stocké entier. Càd, le prefixe, l'année, le numéro de série et les check-digits, tout est mis ensemble dans la colonne AEM_CARD_NUMBER.

Un exemple de numéro est le suivant :
8811004111232329 (j'ignore si le check-digit est valide mais c'est pour l'exemple)
Si on le décortique on a
881 : Préfixe qui ne change pas. Pas besoin de le stocker dans la nouvelle DB donc.
10 : Numéro historique où on l'on faisait partie d'un groupe de plusieurs magasin. Le 10 indiqué que c'était un personnel de chez nous. Depuis, on a quitté ce groupe depuis bien longtemps mais le 10 est resté et il ne change pas. Pas besoin de le stocker non plus donc.
04 : Année de création de la carte. Stockée dans la colonne CRD_YEAR. À l'origine ces deux digits servaient à déterminer une période de validité. Mais cette limite a été supprimée. Il a été décidé depuis d'y mettre l'année de création de la carte mais on n'a pas pour autant refait des nouvelles cartes celle où il représente la limite de validité. Donc au final, ça ne sert à rien si ce n'est éventuellement des stats...
112323 : Numéro de série de la carte. Calculé* dans la colonne CRD_NUMBER pour les cartes qui seront créées avec le nouveau système et stocké dans la colonne OCN_NUMBER pour les cartes de l'ancien système.
29 : Check-digit dont le calcul se fait via un modulo 97 (comme pour les cartes bancaire). Vu que c'est calculé, pas besoin de le stocker.

* En fait, on pourrait totalement se passé de cette colonne mais j'aime bien explicité les choses. Ma décision de la laisser ou non n'est pas encore définitive. C'est un projet encore en cours de développement.

26/02/2016, 12h22
Sebwar

Je ne suis pas sur de tout saisir, dites moi si je me trompe ;)

L'objectif est de d'insérer dans la table CRD les numéro de carte de la table "temporaire" et de garder un liaison entre CRD et la table "temporaire" ?
29/02/2016, 08h35
Kropernic

Hello,

Sorry de la réponse tardive, j'étais en w-e ^^.

L'objectif est "d'importer" les données présentes dans la table temporaire vers les tables CRD et OCN.

En fait, une ligne de la table temporaire doit donner une ligne dans CRD + une ligne dans OCN.

Comme déjà dit, si j'insérais les lignes une à une, je pourrais insérer dans CRD, utiliser SCOPE_IDENTITY() pour récupérer CRD_ID et insérer dans OCN et tout le monde serait content.
Sauf que j'ai un paquet de ligne que j'aimerais bien insérer d'un coup et non pas une à une.
29/02/2016, 09h29
Kropernic

Je pense avoir trouvé la solution en utilisant l'instruction MERGE qui permet d'utiliser des colonnes de la source dans la clause output...

Je dois encore deboguer un peu mais ça a l'air bien parti...

Je confirme que l'instruction merge était bien la clef du problème.

Voici la solution pour mon schéma de db :

Code:

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
DECLARE @OUTPUT TABLE (CRD_ID INT, AEM_CARD_NUMBER VARCHAR(16))
 
MERGE INTO    S_GM.T_CARD_CRD AS TARGET
USING    (    SELECT    AEM_CARD_NUMBER
            FROM    S_TEMP.T_ACTIVE_EMPLOYEE_AEM AEM
                        INNER JOIN S_HR.T_EMPLOYEE_EMP EMP
                            ON    AEM.AEM_NATIONAL_REGISTRY = EMP.EMP_NATIONAL_REGISTRY) AS SOURCE(AEM_CARD_NUMBER)
    ON    1=0
WHEN NOT MATCHED THEN
    INSERT (CRD_YEAR) VALUES (SUBSTRING(SOURCE.AEM_CARD_NUMBER,6,2))
OUTPUT INSERTED.CRD_ID, SOURCE.AEM_CARD_NUMBER INTO @OUTPUT;
 
INSERT INTO S_GM.T_OLD_CARD_NUMBER_OCN(CRD_ID, OCN_NUMBER)
SELECT    CRD_ID,
        SUBSTRING(AEM_CARD_NUMBER,9,6)
FROM    @OUTPUT
 
INSERT INTO S_GM.T_EMPLOYEE_CARD_EMC
SELECT    CRD_ID,
        EMP_ID
FROM    @OUTPUT A
            INNER JOIN S_TEMP.T_ACTIVE_EMPLOYEE_AEM AEM
                ON    A.AEM_CARD_NUMBER = AEM.AEM_CARD_NUMBER
                INNER JOIN S_HR.T_EMPLOYEE_EMP EMP
                    ON    AEM.AEM_NATIONAL_REGISTRY = EMP.EMP_NATIONAL_REGISTRY