oracle SQL Loader 中插入和追加语句的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4812908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference Between Insert and Append statement in SQL Loader?
提问by Rajesh Kumar G
Can any one tell me the Difference Between Insert and Append statement in SQL Loader?consider the below example : Here is my control file
谁能告诉我 SQL Loader 中 Insert 和 Append 语句的区别？考虑下面的例子：这是我的控制文件
load_1.ctl
load data
infile 'load_1.dat' "str '\r\n'"
insert*/+append/* into table sql_loader_1
(
load_time sysdate,
field_2 position( 1:10),
field_1 position(11:20)
)
Here is my data file
这是我的数据文件
load_1.dat
0123456789abcdefghij
**********##########
foo bar
here comes a very long line
and the next is
short
回答by Alex Poole
The documentationis fairly clear; use INSERTwhen you're loading into an empty table, and APPENDwhen adding rows to a table that (might) contains data (that you want to keep).
该文档是相当明确; 利用INSERT当你装载到一个空表，并APPEND以表格添加行时（力量）包含数据（要保留）。
APPENDwill still work if your table is empty. INSERTmight be safer if you're expecting the table to be empty, as it will error if that isn't true, possibly avoiding unexpected results (particularly if you don't notice and don't get other errors like unique index constraint violations) and/or a post-load data cleanse.
APPEND如果你的桌子是空的，它仍然可以工作。INSERT如果您希望表为空，则可能更安全，因为如果不是这样，它会出错，可能会避免意外结果（特别是如果您没有注意到并且没有收到其他错误，例如违反唯一索引约束）和/或加载后数据清理。
回答by hi4ppl
The difference are in two points clear:
区别在于两点很明显：
- append will only add the record if at the end of statement
- insert will insert anywhere you want i.e if your table have 10 column you can insert in 5 column only but in append you can't.
- append 只会在语句末尾添加记录
- insert 将插入您想要的任何位置，即如果您的表有 10 列，您只能插入 5 列，但附加时您不能。
in append both your data and the table should have same columns means insert data in row level rather than in column level
附加您的数据和表应该具有相同的列意味着在行级别而不是在列级别插入数据
and it's also true you cannot use insert if your table have data if it's empty then only you can do use insert.
如果你的表有数据，如果它是空的，那么你不能使用插入也是正确的，那么你只能使用插入。
hope it helps
希望能帮助到你
