Loop insert Query

[TanTrazz]

Hi all,

Im trying to insert records trough an loop but it won't work.

Here is my code

SET RSBtkt = MyConn.execute("SELECT * FROM TABLE")

          WHILE NOT RSBtkt.EOF

              InsertA = "INSERT INTO Table(cell1, cell1, cell1, cell1) VALUES ('"& RSBtkt("cell1") &"','"& RSBtkt("cell2") &"','"& RSBtkt("cell3") &"','"& RSBtkt("cell4") &"')"
              'Response.Write InsertA
              Set DO_INSERTA = MyConn.execute(InsertA)

              RSBtkt.MoveNext
          WEND

Can someone help me please.

TnQ TT

 

[ethorn10]

Is your insert statement really supposed to be inserting into `cell1` four times? I'm guessing it should be `cell1`, `cell2`, `cell3` and `cell4`.

And how do you define "won't work" ... what DOES it do?

 

[gmmastros]

I see a couple things that could be causing you problems, and I know a method to simplify this a lot too.

SET RSBtkt = MyConn.execute("SELECT * FROM TABLE")

          WHILE NOT RSBtkt.EOF

              InsertA = "INSERT INTO Table(cell1, cell[!]2[/!], cell[!]3[/!], cell[!]4[/!]) VALUES ('"& RSBtkt("cell1") &"','"& RSBtkt("cell2") &"','"& RSBtkt("cell3") &"','"& RSBtkt("cell4") &"')"
              'Response.Write InsertA
              [!]Call [/!]MyConn.execute(InsertA)

              RSBtkt.MoveNext
          WEND

Your into clause was specifying the same column.
Also, since your sql query is not returning any rows, all you need to do is call the execute method of the connection object. There's no need to set the results of the output because there aren't any.

Now... for efficiency....

Essentially you are getting all the data in table from your database to ASP, and then you are copying the data back to another table. There's no need for all this 'round-trip' business because you can do the whole thing in the database with a single sql call. This will be MANY times faster. You can replace your entire code block (the stuff you showed here) with...

Call MyConn.execute("Insert Into TABLE(Cell1, Cell2, Cell3, Cell4) SELECT Cell1, Cell2, Cell3, Cell4 FROM TABLE")

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom