Difference Between Insert and Append statement in SQL Loader?

Question

Can any one tell me the Difference Between Insert and Append statement in SQL Loader?consider the below example : 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

score 10 · Accepted Answer · answered Jan 27 '11 at 08:20

10

The documentation is fairly clear; use INSERT when you're loading into an empty table, and APPEND when adding rows to a table that (might) contains data (that you want to keep).

APPEND will still work if your table is empty. INSERT might 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.

answered Jan 27 '11 at 08:20

Alex Poole

183,384
11
179
318

insert also adds the row to the table,append also do the same thing...thwn what is the major difference ?can u explain clearly?i am not able to understand evenafter i went through the document – Rajesh Kumar G Jan 27 '11 at 08:56
1

@Rajesh: `INSERT` will *only* add rows if the table is empty; if the table is not empty then it will error. `APPEND` will add rows whether the table is empty or not. – Alex Poole Jan 27 '11 at 09:20

score -4 · Answer 2 · answered Mar 23 '13 at 12:59

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.

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

Difference Between Insert and Append statement in SQL Loader?

2 Answers2