Sometimes we may have a requirement where we may need to load a large amount of data to a database table. We can achieve this with the help of SQL Loader(We are using the Windows system in this example), where we load bulk data directly from a file into our database table. The source file can be of format .xls, .txt or .csv, etc.
In this article, we will learn how to load bulk data into a database table from a text file using the Windows command prompt.
- OS: Windows 7.
- Database: Oracle 11g.
- SQL Loader : Release 184.108.40.206.0
Create a table and the source file with the required data
We can skip this step in case the database table is already available.
Create a table called EMP_DTLS, with columns holding details about employee ID and employee’s first and last name details as shown below. We use the EMP_DTLS table where we are going to load our data from the text file.
CREATE TABLE EMP_DTLS( EMP_ID INT, EMP_FIRST_NAME VARCHAR(15), EMP_LAST_NAME VARCHAR(15) );
Create a text file called Data.txt with employee data. This file contains the input data that we will store on our newly created table EMP_DTLS.
The text file contains a column header in the first row and column details separated with a comma, as shown below.
EmpID,FirstName,LastName 1,ASB,Notebook 2,Bat,Man 3,Spider,Man
Create the SQL loader control file
Now, the next step is to create a control file.
The control file specifies different control statements, like the source file location, whether to truncate the table before inserting new records, the character used to separate the column data, etc.
In our example control file shown below, we have specified that our:
- Skip the first row as it contains column header names with options keyword.
- Load the data from the file located in the path C:\Users\Desktop\Data.txt
- Truncate the table TEST.EMP_DTLS before inserting the records using truncate keyword.
- Column fields are separated with the character “,”. (We can use “,”, “|”, etc).
- Column data can be enclosed with an optional ‘ ” ‘ character.
- The insertion order for the given data with table column: EMP_ID, EMP_FIRST_NAME and EMP_LAST_NAME.
options ( skip=1 ) load data infile 'C:\Users\Desktop\Data.txt' truncate into table TEST.EMP_DTLS fields terminated by "," optionally enclosed by '"' ( EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME )
Run the SQL loader from command prompt
Finally, run the sqlldr command along with the required details as shown below.
Here, we are specifying the database ‘username/password@Database’ to connect to the database.
We can specify the control file path using the control keyword.
We can specify an optional log keyword to specify the log file path.
The logs are helpful in case of any error occurs during the data loading process.
sqlldr 'db_username/db_password@Database' control='C:\Users\Desktop\loader.ctl' log='C:\Users\Desktop\Results.log'
The below images shows that the SQL loader is loading the data into our database table.
Finally, our database table EMP_DTLS is loaded with data from the source text file.
In this article, we learned how to use SQL loader on the Windows system to load bulk data into our database table.