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
61
62
63
64
65
66
67
68
69
| -- création du jeu d'essai source
create table T1ENT
( T1ID integer primary key
, T1CH char(4) not null
)
;
create table T2DTL
( T1ID integer
, T2DT date not null
, PRIMARY KEY (T1ID, T2DT)
, FOREIGN KEY (T1ID) REFERENCES T1ENT(T1ID)
)
;
insert into T1ENT (T1ID, T1CH)
values (1, 'truc')
, (2, 'azer')
;
insert into T2DTL (T1ID, T2DT)
values (1, '2023-02-14')
, (1, '2023-05-20')
, (2, '2022-11-03')
, (2, '2022-12-15')
, (2, '2022-12-28')
;
-- création de la table de renumérotation ID source <-> ID cible
-- la contrainte PK garantit l'unicité de l'ID source
-- la contrainte UNIQUE garantit l'unicité de l'ID cible
create table T3RENUM
( T3IDOLD integer primary key
, T3IDNEW integer not null
, UNIQUE (T3IDNEW)
)
;
insert into T3RENUM (T3IDOLD, T3IDNEW)
values (1, 11)
, (2, 03)
;
-- creation des tables cibles
create table T1BENT
( T1ID integer primary key
, T1CH char(4) not null
)
;
create table T2BDTL
( T1ID integer
, T2DT date not null
, PRIMARY KEY (T1ID, T2DT)
, FOREIGN KEY (T1ID) REFERENCES T1BENT(T1ID)
)
;
-- alimentation des tables cibles avec ID renumérotés
insert into T1BENT (T1ID, T1CH)
select T3IDNEW, T1CH
from T1ENT as T1
inner join T3RENUM as T3
on T3.T3IDOLD=T1.T1ID
;
insert into T2BDTL (T1ID, T2DT)
select T3IDNEW, T2DT
from T2DTL T2
inner join T3RENUM T3
on T3.T3IDOLD=T2.T1ID
;
-- contrôle des résultats
select * from T1BENT
;
select * from T2BDTL
; |
