output recordset into a table 1

[ultimatewilliam]

hello!

how can i directly output the resultset from a query into a table?

processing the following lines creates a recordset in my computer's memory, I think..

cn.Open ConnectionString
Set rs = cn.Execute("Select * from MyTable", , adCmdText)

thanks!

 

[RoyVidar]

I'm not sure there's any need for a recordset, why not use a query?

[tt]strsql = "insert into table2 select * from mytable"[/tt]

or create a new table

[tt]strsql = "select * into table2 from mytable"

cn.execute strsql,,adexecutenorecords[/tt]

For a recordset, you'd probably need to loop it and add each record, I think.

Roy-Vidar

 

[ultimatewilliam]

i also thought so.. a dual process: create the recordset then loop thru the recordset that would take time if theres a lot of records.

i'll try your first sample. thanks!

 

[ultimatewilliam]

syntax error

 

[ultimatewilliam]

i prefer to create a new table but i recieve the error: syntax error.. i forgot to tell you im using vfpoledb to connect to the database. thanks!

 

[RoyVidar]

I don't know anything about FoxPro, but I think you'll need provider specific SQL. I think my above should work with Access and SQL server.

If you're copying into an existing table, make sure they are identical, else you'd need to specify each column

[tt]"insert into table2 (field1, field2, field3....) " & _
"select myfirst, mythird, mysecond,.... from mytable"[/tt]

From example 5 here SELECT - SQL Command it seems "into" is supported, but I don't know, perhaps add the "Table" keyword? Someone with FoxPro knowledge will perhaps be of better assistance.

Also, for someone to be able to help, I think a bit more of the code wouldn't hurt, also, are there any additional messages than "syntax error"? Which line does it occur on? What are the values of the variables when it occurs (instantiated or not, containing valid values or not)...

Another thought, is to perhaps visit one of the VPF fora?

Roy-Vidar

 

[ultimatewilliam]

thanks roy.. i did it! i tried adding "table" and it worked. thanks!