- rename import_table to import_table_YYYYMMDD
- create empty import_table
- sqlldr import.csv to import_table
Example
@echo off
SET PRJ_DIR=c:\db-import
SET RENAME_SCRIPT=%PRJ_DIR%\sql\rename_table.sql
SET CSV_FILE=%PRJ_DIR%\csv\import.csv
SET CTL_FILE=%PRJ_DIR%\ctl\import.ctl
SET LOG_FILE=%PRJ_DIR%\logs\import.log
::SET TODAY=20200527
set TODAY=%date:~10,4%%date:~4,2%%date:~7,2%
echo %TODAY%
SET TABLE_NAME=import_table
SET RENAME_SQL=RENAME %TABLE_NAME% TO %TABLE_NAME%_%TODAY%;
echo %RENAME_SQL% > %RENAME_SCRIPT%
:: ----------------------------------------
SET DB_USER=
SET DB_PASS=
SET DB_NAME=
:: ----------------------------------------
echo exit | sqlplus %DB_USER%/%DB_PASS%@%DB_NAME% @%RENAME_SCRIPT%
echo sqlldr userid=%DB_USER%/%DB_PASS%@%DB_NAME% data=%CSV_FILE% control=%CTL_FILE% log=%LOG_FILE%
@echo onexit | sqlplus user/pass@yourdb @scriptname
exit | sqlplus -s -l user/pass@yourdb @yoursql.sql > your_log.log
LOAD DATA
INFILE test.dat
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)
กรณีรวมไฟล์ Control กับ Data ไว้ในไฟล์เดียวกัน ใช้สัญลักษณ * เข้ามาแทนที่ ดังตัวอย่าง
LOAD DATA
INFILE *
INTO TABLE foo
FIELDS TERMINATED BY '|'
(i, d DATE 'dd-mm-yyyy')
BEGINDATA
1|01-01-1990
2|4-1-1998
ตัวอย่าง
sqlldr username@server/password control=loader.ctl log=log.txt