|
Prodid |
Prodname |
Mon_Amt |
Tue_Amt |
Wed_Amt |
Thu_Amt |
Fri_Amt |
Sat_Amt |
|
101 102 |
AIWA AKAI |
2000 1900 |
2500 2100 |
2230 2130 |
2900 3100 |
3000 2800 |
2100 2120 |
现在，我打算把 SALES 表中的行，增加到 Week_Sales 表中，增加后的表数据结构如下：
|
Prodid
|
Prodname
|
WeekDay
|
Amount |
|
101 101 101 101 101 101 102 102 102 102 102 102 |
AIWA AIWA AIWA AIWA AIWA AIWA AKAI AKAI AKAI AKAI AKAI AKAI |
Mon Tue Wed Thu Fri Sat Mon Tue Wed Thu Fri Sat |
2000 2500 2230 2900 3000 2100 1900 2100 2130 3100 2800 2120 |
为了达到上面的效果，我们采用Multi table INSERT的语法方式进行插入，语句如下：
Insert all
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Mon’,mon_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Tue’,tue_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Wed’,wed_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Thu’,thu_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Fri’,fri_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Sat’,sat_amt)
Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
Fri_amt,sat_amt from sales;
不但可以插入同一个表，也可以插入到不同表，例如如下的脚本，同时插入suppliers表和customers表中：
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9399028/viewspace-680908/ ，如需转载，请注明出处，否则将追究法律责任。
