Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    42

    Unanswered: CLI/ODBC: multi row insert with single call to SQLExecute?

    Hi All!

    Can someone tell me if it is possible with CLI/ODBC to bind multiple rows of parameters
    and then with a single call to SQLExecute() to insert all those rows at once?

    Of course I could have an insert statement with multiple VALUES sections. That is a valid workaround -
    but it would be more elegant if the statement contains only one VALUES and is prepared as such and
    then just but set of rows to it.

    Without such feature, if I do insert a row per statement, will I have a round trip to the server for every single statement -
    meaning that for every single statement my applicaiton will have to wait for the CLI driver to send a request to the server,
    wait for the server to execute my statement, return a result code to the application, and then it may continue with the next statement?

    Thanks in advance.

    Alex.
    Last edited by akaraivanov; 07-12-06 at 04:54.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    if I do insert a row per statement, will I have a round trip to the server for every single statement ... and then it may continue with the next statement?
    Yes.

    Do you have such an enormous amont of records that have to be inserted that this would be a bottle neck ? You could place all the data that has teo be inserted in a text file and issue an IMPORT or LOAD statement.

    Even if you would perform an IMPORT statement, you would make a round trip to the server.

    Only with a LOAD this will not happen. But then the server needs direct access to the text file to be able to insert the data into the tabel.

    In a test I conducted, inseting 500 000 lines using IMPORT took 20 minutes, using LOAD it took 20 seconds, a seed increase of x 60 !
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2003
    Posts
    42
    Quote Originally Posted by Wim
    Do you have such an enormous amont of records that have to be inserted that this would be a bottle neck ? You could place all the data that has teo be inserted in a text file and issue an IMPORT or LOAD statement.
    In my case, I am trying to make a CLI/ODBC program to have shorter transactions and in that way reduce the time there are row locks hold by the transaction. The use of import/load probably would be not practicle for this type of application.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You could write something like:
    INSERT INTO tablename (ID, columnA) VALUES
    (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

    Thereby reducing the number of client to/from server communication with 75%.

    All the records would be inserted or none. Let's say that the ID 1 was already taken, you'd get a PK violation error and nothing would have been inserted into the table, not even (2, 'B') even if that tuple would have been allowed.

    You could write something like
    INSERT INTO tablename (ID, column) VALUES
    (:hv_id_1, :hv_ca_1), (:hv_id_2, :hv_ca_2), (:hv_id_3, :hv_ca_3), (:hv_id_4, :hv_ca_4);

    with hv_... the host variables, and that for every possible amount of records you could submit in one transaction by your application. This would lead to a chain of

    if nbr_of recs == 1 then
    INSERT INTO tablename (ID, column) VALUES
    (:hv_id_1, :hv_ca_1)
    else if nbr_of recs == 2 then
    INSERT INTO tablename (ID, column) VALUES
    (:hv_id_1, :hv_ca_1), (:hv_id_2, :hv_ca_2);
    ....

    It looks ugly.

    I always design normalised tables and shy from "clever hacks" in my programs. Only when tests show (serious) performance problems will I try denormalisation and clever hacks. Most of the time (and I mean 99.9% of the time) you can solve initial problems in a more elegant way. Denormalisation and clever hacks always come at a price.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2003
    Posts
    42
    Quote Originally Posted by Wim
    You could write something like:
    INSERT INTO tablename (ID, columnA) VALUES
    (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');
    Thank you, Wim! I was considering to try something like that. I was wondering if CLI/ODBC would give me a way to use a statement like "INSERT INTO tablename (ID, colA) VALUES (?,?)" and just be able to bind multiple rows of parameters to it instead. It would be more elegant and would have the same performance effect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •