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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
SQL> create table olddept(
2 dept varchar2(10) primary key,
3 description varchar2(20));
Table created.
SQL> create table oldemp(
2 name varchar2(10) primary key,
3 dept varchar2(10) references olddept);
Table created.
SQL>
SQL> create table newdept(
2 dept varchar2(10),
3 description varchar2(20),
4 id number primary key);
Table created.
SQL> create table newemp(
2 name varchar2(10),
3 dept varchar2(10),
4 id number primary key,
5 deptid number references newdept);
Table created.
SQL>
SQL> create sequence sdept;
Sequence created.
SQL> create sequence semp;
Sequence created.
SQL>
SQL> insert into olddept(dept,description)
2 values ('FINANCE','Finance Department');
1 row created.
SQL> insert into olddept(dept,description)
2 values ('ADMIN','Administration');
1 row created.
SQL> insert into oldemp(name,dept)
2 values ('JOHN','FINANCE');
1 row created.
SQL> insert into oldemp(name,dept)
2 values ('JACK','FINANCE');
1 row created.
SQL> insert into oldemp(name,dept)
2 values ('MARY','ADMIN');
1 row created.
SQL>
SQL> merge into newdept
2 using olddept on (newdept.dept=olddept.dept)
3 when matched then
4 update set description=olddept.description
5 when not matched then
6 insert (dept,description,id)
7 values (olddept.dept,olddept.description,sdept.nextval);
2 rows merged.
SQL>
SQL> merge into newemp
2 using oldemp on (newemp.name=oldemp.name)
3 when matched then
4 update set dept=oldemp.dept,
5 id=semp.nextval,
6 deptid=(select newdept.id
7 from newdept
8 where newdept.dept=oldemp.dept)
9 when not matched then
10 insert (name,dept,id,deptid)
11 values (oldemp.name,oldemp.dept,
12 semp.nextval,
13 (select newdept.id
14 from newdept
15 where newdept.dept=oldemp.dept));
3 rows merged.
SQL>
SQL> select * from oldemp join olddept using (dept);
DEPT NAME DESCRIPTION
---------- ---------- --------------------
FINANCE JOHN Finance Department
FINANCE JACK Finance Department
ADMIN MARY Administration
SQL>
SQL> select * from newemp join newdept on (newemp.deptid=newdept.id);
NAME DEPT ID DEPTID DEPT DESCRIPTION ID
---------- ---------- ---------- ---------- ---------- -------------------- ----------
JACK FINANCE 1 1 FINANCE Finance Department 1
MARY ADMIN 2 2 ADMIN Administration 2
JOHN FINANCE 3 1 FINANCE Finance Department 1
SQL> |
