· 9 years ago · Jan 21, 2017, 10:10 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 set @year = 2016;
29 set @semester = 1;
30 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
31 SELECT @year, @semester, 'V1',13,temp.v1 FROM tempRoom temp where temp.v1 != '' ;
32 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
33 SELECT @year, @semester, 'V2',13,temp.v2 FROM tempRoom temp where temp.v2 != '' ;
34 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
35 SELECT @year, @semester, 'V3',13,temp.v3 FROM tempRoom temp where temp.v3 != '' ;
36 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
37 SELECT @year, @semester, 'V4',13,temp.v4 FROM tempRoom temp where temp.v4 != '' ;
38 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
39 SELECT @year, @semester, 'V5',13,temp.v5 FROM tempRoom temp where temp.v5 != '' ;
40 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
41 SELECT @year, @semester, 'V6',13,temp.v6 FROM tempRoom temp where temp.v6 != '' ;
42 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
43 SELECT @year, @semester, 'V7',13,temp.v7 FROM tempRoom temp where temp.v7 != '' ;
44 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
45 SELECT @year, @semester, 'V8',13,temp.v8 FROM tempRoom temp where temp.v8 != '' ;
46 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
47 SELECT @year, @semester, 'V9',13,temp.v9 FROM tempRoom temp where temp.v9 != '' ;
48 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
49 SELECT @year, @semester, 'V10',13,temp.v10 FROM tempRoom temp where temp.v10 != '' ;
50 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
51 SELECT @year, @semester, 'V11',13,temp.v11 FROM tempRoom temp where temp.v11 != '' ;
52 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
53 SELECT @year, @semester, 'V12',13,temp.v12 FROM tempRoom temp where temp.v12 != '' ;
54 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
55 SELECT @year, @semester, 'V13',13,temp.v13 FROM tempRoom temp where temp.v13 != '' ;
56 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
57 SELECT @year, @semester, 'V14',13,temp.v14 FROM tempRoom temp where temp.v14 != '' ;
58 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
59 SELECT @year, @semester, 'V15',13,temp.v15 FROM tempRoom temp where temp.v15 != '' ;
60 INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)
61 SELECT @year, @semester, 'V16',13, temp.v16 FROM tempRoom temp where length(temp.v16) != 1 ;
62
63
64 DROP TEMPORARY TABLE IF EXISTS tempRoom;