VfveNsfR

1DROP TEMPORARY TABLE IF EXISTS tempCourse; 
2SET SQL_SAFE_UPDATES = 0;
3set @year=2017; 
4set @semester=1;
5CREATE TEMPORARY TABLE IF NOT EXISTS tempCourse ( 
6`no`              INT(11)     NOT NULL, 
7`company_account` VARCHAR(20) NOT NULL, 
8`course`          VARCHAR(10) NOT NULL, 
9`description`     VARCHAR(100) DEFAULT NULL, 
10`capacity`         INT(11)     NOT NULL, 
11`total_balance`   DOUBLE       DEFAULT NULL, 
12`price`           DOUBLE       DEFAULT NULL, 
13`start_date`      DATE         DEFAULT NULL, 
14`end_date`        DATE         DEFAULT NULL, 
15`start_time`      TIME         DEFAULT NULL, 
16`ledger_bal`      VARCHAR(100) DEFAULT NULL, 
17`type`            VARCHAR(100) DEFAULT NULL 
18); 
19DELETE FROM `course` WHERE year= @year AND semester=@semester ; 
20TRUNCATE TABLE tempCourse; 
21LOAD DATA LOCAL INFILE 'E:/bio beam/uploads/course.csv' INTO TABLE tempCourse 
22FIELDS TERMINATED BY ',' 
23ENCLOSED BY '\"' 
24LINES TERMINATED BY '\n' 
25IGNORE 1 LINES; 
26
27UPDATE tempCourse SET course =TRIM(TRAILING '\r' FROM start_time); 
28UPDATE tempCourse SET course =TRIM(TRAILING '\n' FROM start_time); 
29
30-- INSERT INTO `bio_beam_register`.`course` (`company_account`, `year`, `semester`, `source`, `course`, `description`, `capacity`, `total_balance`, `price`, `start_date`, `end_date`, `start_time`, `branch`) 
31SELECT 
32temp.company_account, 
33@year, 
34@semester, 
35'BANK', 
36temp.course, 
37temp.description, 
38temp.capacity, 
39temp.total_balance, 
40temp.price, 
41temp.start_date, 
42temp.end_date, 
43temp.start_time, 
44'PAYATHAI' 
45FROM tempCourse temp ;
46 select * from tempCourse;
47DROP TEMPORARY TABLE IF EXISTS tempCourse;