LOAD DATA
APPEND
INTO TABLE my_table
FIELDS TERMINATED BY ','
(
CITY_CODE ,
DEALER_ID ,
DEALER_NAME ,
WHOLE_NAME ,
DEALER_KIND ,
DEALER_GRADE ,
DEFAULT_SALE_KIND ,
BELONGS_PART ,
VALID_STATUS ,
CONTRACT ,
CONTRACT_BEGIN_DATE date(14) "YYYYMMDDHH24MISS",
CONTRACT_END_DATE date(14) "YYYYMMDDHH24MISS",
CAUTION_MONEY ,
CORPORATION ,
IDENTITY ,
IDENTITY_CODE ,
TAX_CODE ,
ADDRESS ,
ZIP ,
CONTACT_PERSON ,
CONTACT_PHONE ,
REMARK ,
PRESERVE01 ,
PRESERVE02)这是一个例子,把表名和列名替换一下就行了。
APPEND
INTO TABLE my_table
FIELDS TERMINATED BY ','
(
CITY_CODE ,
DEALER_ID ,
DEALER_NAME ,
WHOLE_NAME ,
DEALER_KIND ,
DEALER_GRADE ,
DEFAULT_SALE_KIND ,
BELONGS_PART ,
VALID_STATUS ,
CONTRACT ,
CONTRACT_BEGIN_DATE date(14) "YYYYMMDDHH24MISS",
CONTRACT_END_DATE date(14) "YYYYMMDDHH24MISS",
CAUTION_MONEY ,
CORPORATION ,
IDENTITY ,
IDENTITY_CODE ,
TAX_CODE ,
ADDRESS ,
ZIP ,
CONTACT_PERSON ,
CONTACT_PHONE ,
REMARK ,
PRESERVE01 ,
PRESERVE02)这是一个例子,把表名和列名替换一下就行了。
解决方案 »
- 用delphi的ado连接oracle却找不到我写的存储过程?
- 关于Oracle中的<>
- 关于视图的一个简单问题,如何查看视图的创建逻辑。
- 新手求救:plsql中数组定义
- jsp 对oracle 表的查询结果无显示
- 我的os是2000,安装了oracle817后,新建sid但是不能用dba管理
- sqlplus里只想看sql语句的执行时间,不想看查询的结果(太多),应怎样设置?
- 急!Oracle OLAP 技术难题和询问 (入者给分)
- 如何从数据表中SELECT出字段为空,但不为.NULL的数据??急急急,等。。。
- 这个语句是否兼容于oracle7?
- (fzymr) 这个sql怎么写?
- Form 中的快捷键如何设计?
infile 'mydata.dat'
append into table mytable
fields terminated by ","
char1 position(*) char,
char2 position(*) char,
number1 position(*) integer external,
char3 position(*) char,
char4 position(*) char,
number2 position(*) integer external,
number3 position(*) integer external,
number4 position(*) integer external,
char5 position(*) char,
char6 position(*) char,
char7 position(*) char
)注意你的文数据文件的最后一个‘,’前后都没有数据故只能插入11个字段
---------------------------------------------------------------------/*
||
|| Description:
|| Given a tablename generates
|| 1. Flat ascii file with delimiters
|| 2. SQL*Load control file to load the data
||
*/set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause offaccept vtbl_name prompt 'Enter table name: 'define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"define dataname = &&vtbl_name..datspool data.sqlprompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt || Script Name: data.sql
prompt || Author: Ramesh K Meda
prompt || Date: Feb 1995 (Does day matter?)
prompt || Copyright info: Feel free to copy!
prompt || Fees: As you please!
prompt */prompt clear columns--
-- Set up column formats
--select 'column ' || column_name || ' format ' ||
DECODE (data_type,
'DATE', 'A14'
,'NUMBER' ,
decode (data_scale, 0,
rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
, rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
|| '.' ||
rpad('0', nvl(data_scale, 5), '0'))
,'A' || data_length
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout offprompt spool &datanameprompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout onspool off--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctlprompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&vtbl_name')
order by column_id
/prompt )spool offclear scree
prompt Generating data [email protected] Files generated:
prompt SQL script: data.sql
prompt data file: &&vtbl_name..dat
prompt control: &&vtbl_name..ctl