Results 1 to 4 of 4

Thread: Inserting Multiple Rows

  1. #1
    Join Date
    Dec 2002
    Posts
    6

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,926
    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.

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    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.

  4. #4
    Join Date
    Aug 2007
    Posts
    1
    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
  •