Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    59

    Unanswered: Insert multiple rows as an array....

    Hi all,

    I want to insert multiple data in once....

    Example: That's how my code works now...

    "cnn.execute" executes this code each time inside loop and then I'm taking datas from another table and writing my table....


    do while not RS.Eof

    sqlstr2="insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("cID") & "')"

    cnn.execute(sqlstr2)
    rs.MoveNext
    loop

    what i want to do is something like below....

    do while not RS.Eof

    array1= array1 + "," + rs("ctiID")
    array2= array2 + "," + rs("name")
    array3= array3 + "," + rs("cID")

    rs.MoveNext
    loop

    sqlstr2="insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & array1 & "','" & array2 & "','" & array3 & "')"

    cnn.execute(sqlstr2)

    The select statement below but I want to use it as insert statement....

    here "extension " and "user" variables are array...

    Select * from T_NBS_INTERNAL WHERE (EXTENSION IN (" &extension&") OR USER_CODE IN (" &user&"))"

    So what i'm lookin for is something like

    Insert into T_NBS_INTERNAL values (EXTENSION IN (" &extension&") OR USER_CODE IN (" &user&"))"

    Thanx in advance....

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Insert multiple rows as an array....

    mybe this one helps:


    sqlstr2=""
    do while not RS.Eof

    sqlstr2 = sqlstr2 & " insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("cID") & "')"

    rs.MoveNext
    loop
    cnn.execute(sqlstr2)

  3. #3
    Join Date
    Nov 2002
    Posts
    59

    nope

    it doesnt work....
    cuz it's concatenates and tries to execute the whole sql sentence and gives error.... cuz its meaningless

    it's somethin like
    insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("ctiID") & "') insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("ctiID") & "') insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("ctiID") & "') insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values('" & rs("ctiID") & "','" & rs("name") & "','" & rs("ctiID") & "')


    but thanx anyways at least u gave a try....

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: nope

    Originally posted by cuneyt
    it doesnt work....
    cuz it's concatenates and tries to execute the whole sql sentence and gives error.... cuz its meaningless

    at least in pure sql it is allowed to concatenate like this:
    insert ...
    insert ...
    insert ...

    what is the error message ?

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    insert ...
    select ... union all
    select ... union all
    select ...

  6. #6
    Join Date
    Nov 2002
    Posts
    59

    Re: nope

    error msg is: sql command not properly ended....

    Originally posted by msieben
    at least in pure sql it is allowed to concatenate like this:
    insert ...
    insert ...
    insert ...

    what is the error message ?

  7. #7
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: nope

    OK, i think you sqlcmd is not really valid, try it like this:

    s1=""
    do while not RS.Eof

    val= rs("ctiID") & "," & rs("name") & "," & rs("cID")

    s1=s1 & " insert into T_NBS_CCM_LIST(CTI,NAME,CLUSTERID) values( " & val & ")"

    rs.MoveNext
    loop
    cnn.execute(s1)


    Originally posted by cuneyt
    error msg is: sql command not properly ended....

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    create table #Test(X int)
    insert #Test(X)
    select 0 union all
    select 1 union all
    select 2
    select * from #Test

    It is usable, but takes some time for Query Optimiser to pass the code.
    The problem with INSERT is, that it returns how many rows affected for each command, prevent it by use of "SET NOCOUNT ON".

    The best bulk insert without BULK INSERT:

    SET NOCOUNT ON
    INSERT ...
    INSERT ...
    INSERT ...
    INSERT ...
    ... up to 50 inserts, more ones take too much time to optimize

    You can also replace a raw insert by your SP to improve speed, it can be pre-optimized.

    Good luck!

  9. #9
    Join Date
    Nov 2002
    Posts
    59

    thanx

    thank u guys, i think the problem was that i was takin the datas from SQL Sevrver and write into Oracle DB so the problem is Oracle cannot read multiple "insert" together.... the error i mentioned above belongs to Oracle, SQL server compiles it with no problem... but i still dont know how it will work in Oracle...
    thanx...

Posting Permissions

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