if we are using oracle database and are in delima how to insert data in to it. The simplest way is to insert the data using one to one entry using simple database command, However this may not be practical in a case where we need to insert large amount of data. so inorder to insert bulk amount of data instantly , there are basically two efficient way.
sql developer is an easy and efficient tool in oracle database management. it faciliates the importing of data in a bulk and many others . It makes the data import in csv or any other format data to import in to tables easy .
here are the steps in importing the data
1: create the table with columns name in oracle database.
example
2:create the csv format of data to be inserted. note: be sure that the constrants and the data type matches to the csv files and to that of table, otherwise error will be notified.
example:
CUST_INCOME_KEY,CUST_INCOME_IDNT,CUST_INCOME_MNTH
3: right click on table in which data is to be inserted. Then click on import data from the option available.
4:select the source file from the place where csv file is saved.Data preview will be launched ,verify the data and click next
5:choose column will be launched,the columns to be used are selected from moving columns from available to selected column then click next
6:column defination: source files are mapped to the destination table i.e if we want the column CUST_INCOME_KEY of csv file to be mapped to CUST_INCOME_KEY in the table then choice can be made here itself
7:finally finish the data insertion. if errors occurs do correct the errors and repeat it.
THE NEXT WAY IS
- use of oracle sql developer
sql developer is an easy and efficient tool in oracle database management. it faciliates the importing of data in a bulk and many others . It makes the data import in csv or any other format data to import in to tables easy .
here are the steps in importing the data
1: create the table with columns name in oracle database.
example
2:create the csv format of data to be inserted. note: be sure that the constrants and the data type matches to the csv files and to that of table, otherwise error will be notified.
example:
CUST_INCOME_KEY,CUST_INCOME_IDNT,CUST_INCOME_MNTH
1,4,5000
2,6,6000
3,5,7000
4,7,8000
------------
-------------
3: right click on table in which data is to be inserted. Then click on import data from the option available.
4:select the source file from the place where csv file is saved.Data preview will be launched ,verify the data and click next
5:choose column will be launched,the columns to be used are selected from moving columns from available to selected column then click next
6:column defination: source files are mapped to the destination table i.e if we want the column CUST_INCOME_KEY of csv file to be mapped to CUST_INCOME_KEY in the table then choice can be made here itself
7:finally finish the data insertion. if errors occurs do correct the errors and repeat it.
THE NEXT WAY IS
- use of SQL loader
next way of insertion is the use of sql loader. in this method we have the data file in mostly in csv format . Then we create a control file and execute the command to insert the data in bulk.
note: this is an example of inserting data through csv file with linux using sql loader in a remote host.
steps of importing data
1: create the table in oracle database.
let us suppose we have a table named CUST_INCOME_DB with columns
CUST_INCOME_KEY;
CUST_INCOME_IDNT;
CUST_INCOME_MNTH;
2: create the csv file and store in appropriate place
suppose we have income.csv as
example
CUST_INCOME_KEY,CUST_INCOME_IDNT,CUST_INCOME_MNTH
1,4,5000
2,6,6000
3,5,7000
4,7,8000
------------
-------------
3:create the control file:
e.g let us suppose that we create the income.ctl file as
Example:
OPTIONS(skip=1) //skip is used to skip the row , skip=1 skips the first 1 row of csv
file
LOAD DATA
INFILE 'income.csv'
INTO TABLE CUST_INCOME_DM
FIELDS TERMINATED BY ','
(
CUST_INCOME_KEY,
CUST_INCOME_IDNT,
CUST_INCOME_MNTH
)
4 execute the command as
sqlldr username/psswd@SID control=income.ctl log=income.log
bad=income.bad
discard=income.discard
example:
sqlldr
BANKING_TBL01/yomari@BANKINDEV control=income.ctl log=income.log
bad=income.bad
discard=income.discard
note: in local host SID is not needed.
