控制函数:
OPTIONS (skip=1,rows=128)
LOAD DATA
INFILE "test.csv"
REPLACE INTO TABLE usr_pms.pmm_safestock
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
ps_id RECNUM,
ps_item,
ps_des,
ps_qty,
ps_type
)
数据csv:
ITEM,Des.,safestock,type
4170001800,MCU包装,100,1
4170001802,MCU包装,100,1
4170001800,MCU包装,100,1
4170001800,MCU包装,100,1
4170001803,MCU包装,101,2
4170001803,MCU包装,103,3
问题:每次运行控制文件时都会把原来表里的数据清空,然后写入新的数据,我想实现的功能是保留原有表中的特定行,列如:导入新数据csv:
ITEM,Des.,safestock,type
4170001802,MCU包装,101,2
4170001804,MCU包装,103,3
4170001804,MCU包装,103,3
4170001804,MCU包装,103,5
保留表中原有type=1的数据,然后把type!=1的数据清空,再把新的数据添加到表里,不知道是否能实现这个功能,如果能的话,请大家教我一下(刚接触sqlldr不太懂),谢谢!
OPTIONS (skip=1,rows=128)
LOAD DATA
INFILE "test.csv"
REPLACE INTO TABLE usr_pms.pmm_safestock
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
ps_id RECNUM,
ps_item,
ps_des,
ps_qty,
ps_type
)
数据csv:
ITEM,Des.,safestock,type
4170001800,MCU包装,100,1
4170001802,MCU包装,100,1
4170001800,MCU包装,100,1
4170001800,MCU包装,100,1
4170001803,MCU包装,101,2
4170001803,MCU包装,103,3
问题:每次运行控制文件时都会把原来表里的数据清空,然后写入新的数据,我想实现的功能是保留原有表中的特定行,列如:导入新数据csv:
ITEM,Des.,safestock,type
4170001802,MCU包装,101,2
4170001804,MCU包装,103,3
4170001804,MCU包装,103,3
4170001804,MCU包装,103,5
保留表中原有type=1的数据,然后把type!=1的数据清空,再把新的数据添加到表里,不知道是否能实现这个功能,如果能的话,请大家教我一下(刚接触sqlldr不太懂),谢谢!
merge into作用如果表中记录已经存在,则更新,如果不存在则插入
也可以有where条件条件限制,
用append into
If the tables you are loading into already contain data, you have three options:APPENDREPLACETRUNCATEAPPEND
If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded.REPLACE
With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.TRUNCATE
The SQL TRUNCATE statement quickly and efficiently deletes all rows from a table or cluster, to achieve the best possible performance.