I need to collect a series of results from one table and then insert them into a new table using a new SQL command for each result from the original recordset. I.E:
oledbconnection1.open()
sSQL = "SELECT * FROM Table1"
oledbcommand1.commandtext = sSQL
rs = oledbcommand1.executeReader
rs.Read()
oledbconnection2.open()
do while rs.HasRows
sSQL = "INSERT INTO Table2 (field1) VALUES (" & rs(0) & ")"
oledbcommand2.commandtext = sSQL
oledbcommand2.executeNonQuery()
rs.Read()
'// now what??? I can't reuse oledbcommand2
'// and it's associated oledbconnection2
loop
One thought would be to create a batch query (i.e. concactentate the insert statements and then run that on a single oledbcommand) but is that allowed, and if so, how should each SQL query be separated? Semicolons?
There are potentially 20 or more insert queries so I need something fairly dynamic. Is it possible to create an array of connection and command objects (like you could with VB 6 objects like labels and buttons)?
Any help appreciated.Give this a go.
INSERT INTO Table2 (field1) (SELECT * FROM Table1)
oledbconnection1.open()
sSQL = "SELECT * FROM Table1"
oledbcommand1.commandtext = sSQL
rs = oledbcommand1.executeReader
rs.Read()
oledbconnection2.open()
do while rs.HasRows
sSQL = "INSERT INTO Table2 (field1) VALUES (" & rs(0) & ")"
oledbcommand2.commandtext = sSQL
oledbcommand2.executeNonQuery()
rs.Read()
'// now what??? I can't reuse oledbcommand2
'// and it's associated oledbconnection2
loop
One thought would be to create a batch query (i.e. concactentate the insert statements and then run that on a single oledbcommand) but is that allowed, and if so, how should each SQL query be separated? Semicolons?
There are potentially 20 or more insert queries so I need something fairly dynamic. Is it possible to create an array of connection and command objects (like you could with VB 6 objects like labels and buttons)?
Any help appreciated.Give this a go.
INSERT INTO Table2 (field1) (SELECT * FROM Table1)