| 22 |
rodolico |
1 |
insert into camp2.client( client_id,name,notes,internal_notes,added_date,removed_date ) select client_id,name,notes,internal_notes,added_date,removed_date from camp.client;
|
|
|
2 |
update camp2.client set notes = null where length( notes ) = 0;
|
|
|
3 |
update camp2.client set internal_notes = null where length( internal_notes ) = 0;
|
|
|
4 |
|
|
|
5 |
/* load site */
|
|
|
6 |
insert into camp2.site( site_id,name,notes,added_date,removed_date )
|
|
|
7 |
select site_id,name,notes,added_date,removed_date
|
|
|
8 |
from camp.site;
|
|
|
9 |
|
|
|
10 |
/* load device types, convert show_as_system to flags */
|
|
|
11 |
insert into camp2.device_type( device_type_id,name,flags,added_date,removed_date)
|
|
|
12 |
select device_type_id,name,if(show_as_system='Y',1,0),added_date,removed_date
|
|
|
13 |
from camp.device_type;
|
|
|
14 |
|
|
|
15 |
/* load only actual devices into device */
|
|
|
16 |
insert into camp2.device( device_id,device_type_id,name,notes,added_date,removed_date )
|
|
|
17 |
select device_id,device_type_id,name,notes,added_date,removed_date
|
|
|
18 |
from camp.device
|
|
|
19 |
where camp.device.device_type_id in (
|
|
|
20 |
select device_type_id
|
|
|
21 |
from camp.device_type
|
|
|
22 |
where camp.device_type.show_as_system = 'Y'
|
|
|
23 |
);
|
|
|
24 |
|
|
|
25 |
/* now, populate joining tables site_device and client_device and client_site */
|
|
|
26 |
insert into camp2.site_device (site_id,device_id,added_date,removed_date)
|
|
|
27 |
select site_id,device_id,added_date,removed_date
|
|
|
28 |
from camp.device
|
|
|
29 |
where device_type_id in ( select device_type_id from camp.device_type where show_as_system = 'Y');
|
|
|
30 |
|
|
|
31 |
insert into camp2.client_device (client_id,device_id,added_date,removed_date)
|
|
|
32 |
select client_id,device_id,device.added_date,device.removed_date
|
|
|
33 |
from camp.device join camp.site using (site_id)
|
|
|
34 |
where device_type_id in ( select device_type_id from camp.device_type where show_as_system = 'Y');
|
|
|
35 |
|
|
|
36 |
insert into camp2.client_site ( client_id,site_id,added_date,removed_date )
|
|
|
37 |
select client_id,site_id,added_date,removed_date
|
|
|
38 |
from camp.site;
|
|
|
39 |
|
|
|
40 |
|
|
|
41 |
|
|
|
42 |
/* and fill out device_relationship */
|
|
|
43 |
|
|
|
44 |
insert into camp2.device_device( device_id, parent_id, added_date, removed_date )
|
|
|
45 |
select camp2.device.device_id, camp.device.part_of, camp2.device.added_date,camp2.device.removed_date
|
|
|
46 |
from camp2.device join camp.device using (device_id)
|
|
|
47 |
where camp.device.part_of is not null;
|