Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Minnesota
    Posts
    7

    Question Unanswered: Combine multiple sql calls into 1

    I have an old app that I'm trying to recode and improve performance.

    From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.

    Here is a quick example of the script

    Select * from table1 where id = " & tempVariable
    If Not RS.EOF Then
    strTable1 = RS("SomeRec")
    Else
    RS.ADDNEW
    RS("SomeRec") = tempRec1
    RS.UPDATE
    RS.Requery
    strTable1 = RS("SomeRec")
    End If
    RS.CLOSE

    Select * from table2 where id =2
    If Not RS.EOF Then
    strTable2 = RS("SomeRec")
    Else
    RS.ADDNEW
    RS("SomeRec") = tempRec2
    RS.UPDATE
    RS.Requery
    strTable2 = RS("SomeRec")
    End If
    RS.CLOSE

    Select * from table3 where id =3
    If Not RS.EOF Then
    strTable3 = RS("SomeRec")
    Else
    RS.ADDNEW
    RS("SomeRec") = tempRec3
    RS.UPDATE
    RS.Requery
    strTable3 = RS("SomeRec")
    End If
    RS.CLOSE

    INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'


    These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
    Thanks
    -Scott

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Check out the INSERT ... EXECUTE(' ') syntax in BOL.

Posting Permissions

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