Difference between insert statement and append in SQL loader?

Question

Can someone tell me the difference between Insert and Append statement in SQL Loader? consider the example below: Here is my control file

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 

Answer 1

The documentation is pretty straightforward; use INSERT when loading into an empty table and APPEND when adding rows to a table that (may) contain data (which you want to save).

APPEND will work if your table is empty. INSERT can be safer if you expect the table to be empty, because it will be an error, if it is incorrect, possibly avoiding unexpected results (especially if you do not notice and get other errors, such as a unique index of violation of restrictions) and / or clearing data after loading.

Answer 2

The difference in two points is clear:

• append will only add an entry if at the end of the insert statement
• will be inserted anywhere. If your table has 10 columns, you can insert only 5 columns, but you cannot in the append.

add both your data and the table should have the same columns, means inserting data at the row level, not at the column level

and this is also true, you cannot use insertion if there is data in your table, if it is empty, and only you can use insert.

hope this helps