-
Inserting Multiple Rows
As part of a project I have to write a java application that will read a text file and then send the values to a specified database.
I have a limited knowledge of SQL and don't know if I can do the following.
If my database has 3 columns and the text file appears like this
val1,val2,val3
val1,val2,val3
Can I write an SQL statement like this,
INSERT INTO Table VALUES('val1','val2','val3','val1','val2','val3')
Hence both rows are inserted at the same time?
Or do I have to write a loop to insert each row, individually?
Thanks in advance.
-
You can't.
You have to use database specify utility to load data in a text file. If you are using Oracle see SQL Loader.
If you are using SQL Server or Sybase, you can use bcp utility.
If you are using SQL Server you have another choice of BULK INSERT command.
-
You can simply code you app to loop the records.
Have some psuedo code.
do while not EOF
populate variables from text file
do the insert
REAL SQL: INSERT INTO myTable (col1,col2, col3) VALUES (" + val1 + ", " + val2 + ", " + val3 + "');"
loop
You will need to have the values cleaned from any characters that could invalidate your SQL statement such as the ' .
Depending on the Quantity of Data, you *MAY* wish to turn off logging for the duration of the load, but I would heartily advise against it.
-
INSERT INTO employees
select * from
(select 7698, 'Blake', 'Manager' from dual) union
(select 1235, 'Bob', 'Executive' from dual) union
(select 5465, 'Joe', 'Engineer' from dual);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|