Data Loading Using Sql Loader

Doc By – Mahesh Devalekar

DBA

Procedure to Insert Data Into the Database Using SQL Loader Utility STEP 1 :If the flat file is in the “.xls” format, then save that file in the “.csv” format. STEP 2 :Create the table in the particular user’s schema into which you want to insert the flat data. This new table’s columns name & its datatypes must match the flat data’s columns name & its datatypes. STEP 3 :Create the control file & save it as “.ctl” extention. The controlfile should be like as LOAD DATA INFILE * INTO TABLE emp FIELDS TERMINATED BY ',' (EMP_ID,DEP_ID,CITY_ID,SALARY) begindata 1,1001,50,15000 2,1002,50,25000 3,1003,60,20000 4,1004,70,40000 5,1005,70,1000 6,1006,80,2000 7,1007,90,3000 8,1008,90,4000 9,1009,40,50000 10,1010,30,10000 In the above control file, the data after “begindata” keyword is copied from the file which is saved in “.csv” format in the STEP 1 STEP 4 :Connect to the command prompt window or to the terminal window as per the available environment & specify the following parameters of SQL Loader utility. sqlldr userid=scott/tiger control=%file_path%.ctl log=%file_path%.log bad=%file_path%.log All the commited data will insert into the specified table using SQL Loader utility.