Wednesday 24 December 2014

SQL*LOADER

Loader Method (1)

CREATE THE STRUCTUER OF TABLE:

CREATE TABLE T(EMPNO NUMBER,EMPNAME VARCHAR(20),SAL NUMBER,DEPTNO NUMBER);

====================================================================

CREATE LOADER FILE (ctl) FILE AS Fallows
load data
infile 'c:\data\mydata.csv'
into table T
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )


And save the file with any name but with the extension of .CTL in any location

Create the the datafile in which all the data u have for inset into the table

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

Save the datafiel with 'c:\data\mydata.csv' name :

Now fire the command in c:\

sqlldr HR/HR control=C:\DATA\A.ctl



Loader Method (2)
Create the structure of table in hr as fallows:

create table d(dept char(4),deptname char(20));

than create a .CTL file as given below in the any location and save with .CTL

load data
infile *
replace
into table d
( dept position (01:05) char(4),
deptname position (08:27) char(20))
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

Use the following Command on c:\ for insert data into hr table d.

sqlldr HR/HR control=C:\DATA\F.ctl


Loader Method (3)
Excel File uploads into database:

Create the Structure of Table l into database

create table l(rollno number,sname varchar(10),sal number);

create a excel file with data into it and save it into any location with .CSV Format and make the .CTL file for loading data of excel file like that.

load data
infile 'c:\data\dil.csv'
into table l
fields terminated by "," optionally enclosed by '"'
( rollno, sname, sal )

Fire the Command into the C:\

sqlldr HR/HR control=C:\data\l.ctl

No comments: