I'm trying to optimise the loading of some data into somes tables from external files, at present I use load table to load the data into a temporary staging table, I use an INSERT SELECT with ON EXISTING SKIP so as to only insert 'new' data into the table. I suspect there is not much scope for me to improve upon this, but if anyone knows otherwise I would really like some suggestions.
1. bcp in the data into a temporary table, no need to be a hash(#) table.
bcp command details can be found here
2. then use insert command, to make sure only new entries are getting inserted.
insert into DESTINATION_TABLE ( col_1, col_2, col_3...)
select col_1, col_2, col_3...
from TEMPORARY_TABLE A
WHERE not exists
( select 1 from DESTINATION_TABLE B
where A.col_1 = B.col_1
and A.col_2 = B.col_2
and A.col_3 = B.col_3 )
Note : If you table size is considerably big, make sure you have proper indexes defined.
Alternatively, if your file is having the all the latest data which you want to be available in your table, you can simply bcp in the complete data, but this may need extra caution as you are playing with existing data as well. But very much doable.