· 9 years ago · Jan 21, 2017, 10:08 AM
1
2 CREATE TEMPORARY TABLE IF NOT EXISTS tempRoom (
3 `v1` VARCHAR(10) NOT NULL,
4 `v2` VARCHAR(10) NOT NULL,
5 `v3` VARCHAR(10) NOT NULL,
6 `v4` VARCHAR(10) NOT NULL,
7 `v5` VARCHAR(10) NOT NULL,
8 `v6` VARCHAR(10) NOT NULL,
9 `v7` VARCHAR(10) NOT NULL,
10 `v8` VARCHAR(10) NOT NULL,
11 `v9` VARCHAR(10) NOT NULL,
12 `v10` VARCHAR(10) NOT NULL,
13 `v11` VARCHAR(10) NOT NULL,
14 `v12` VARCHAR(10) NOT NULL,
15 `v13` VARCHAR(10) NOT NULL,
16 `v14` VARCHAR(10) NOT NULL,
17 `v15` VARCHAR(10) NOT NULL,
18 `v16` VARCHAR(10) NOT NULL
19 );
20 TRUNCATE TABLE room_plan;
21 TRUNCATE TABLE tempRoom;
22 LOAD DATA LOCAL INFILE 'E:/bio beam/assets/fileToImport/roomseat.csv' INTO TABLE tempRoom
23 FIELDS TERMINATED BY ','
24 ENCLOSED BY '\"'
25 LINES TERMINATED BY '\n'
26 IGNORE 1 LINES;
27 SET SQL_SAFE_UPDATES = 0;
28
29 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
30 SELECT 2016, 1, 'V1',13,temp.v1 FROM tempRoom temp where temp.v1 != '' ;
31 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
32 SELECT 2016, 1, 'V2',13,temp.v2 FROM tempRoom temp where temp.v2 != '' ;
33 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
34 SELECT 2016, 1, 'V3',13,temp.v3 FROM tempRoom temp where temp.v3 != '' ;
35 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
36 SELECT 2016, 1, 'V4',13,temp.v4 FROM tempRoom temp where temp.v4 != '' ;
37 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
38 SELECT 2016, 1, 'V5',13,temp.v5 FROM tempRoom temp where temp.v5 != '' ;
39 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
40 SELECT 2016, 1, 'V6',13,temp.v6 FROM tempRoom temp where temp.v6 != '' ;
41 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
42 SELECT 2016, 1, 'V7',13,temp.v7 FROM tempRoom temp where temp.v7 != '' ;
43 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
44 SELECT 2016, 1, 'V8',13,temp.v8 FROM tempRoom temp where temp.v8 != '' ;
45 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
46 SELECT 2016, 1, 'V9',13,temp.v9 FROM tempRoom temp where temp.v9 != '' ;
47 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
48 SELECT 2016, 1, 'V10',13,temp.v10 FROM tempRoom temp where temp.v10 != '' ;
49 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
50 SELECT 2016, 1, 'V11',13,temp.v11 FROM tempRoom temp where temp.v11 != '' ;
51 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
52 SELECT 2016, 1, 'V12',13,temp.v12 FROM tempRoom temp where temp.v12 != '' ;
53 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
54 SELECT 2016, 1, 'V13',13,temp.v13 FROM tempRoom temp where temp.v13 != '' ;
55 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
56 SELECT 2016, 1, 'V14',13,temp.v14 FROM tempRoom temp where temp.v14 != '' ;
57 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
58 SELECT 2016, 1, 'V15',13,temp.v15 FROM tempRoom temp where temp.v15 != '' ;
59 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
60 SELECT 2016, 1, 'V16',13, temp.v16 FROM tempRoom temp where length(temp.v16) != 1 ;
61
62
63 DROP TEMPORARY TABLE IF EXISTS tempRoom;