Recordset to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

From a user defined function on my SQL server, I return a recordset. From
that recordset, I want to make a temporary table that I can use in several
forms and several queries. I intend on dropping the table after users log
off or at a later time in the day.

Can someone give me the syntax to create this table on the fly?

tia,
 
SELECT field1, field2, field3 INTO newTableName FROM
yourOtherTable WHERE Some criteria GROUPBY something
HAVING more criteria
this is your bare bones make table query. I have never use
one in a record set so something seems odd.
but i would use a temp table. instead of dropping it i
would just delete the contents and use the same table the
next time. that way all you have to do is insert the
contents of the recordset into the temp table.
 
Thanks for the pseudo code and the advise. The reason I wanted to create a
new table is that I will have several users online at the same time running
the same routine; filling the same table from the recordset. Having a new
table for each user created when they log on sounded like a way to keep
things separate. It might be better to just add another field and load it
with the user's ID. That way I can sort or query against the existing table
based on their unique records.

Any thoughts on that plan of action?

JMorrell
[email protected] said:
SELECT field1, field2, field3 INTO newTableName FROM
yourOtherTable WHERE Some criteria GROUPBY something
HAVING more criteria
this is your bare bones make table query. I have never use
one in a record set so something seems odd.
but i would use a temp table. instead of dropping it i
would just delete the contents and use the same table the
next time. that way all you have to do is insert the
contents of the recordset into the temp table.
Click to expand...
 
if you do have several users in the same database running
the same routine, that presents a nameing problem with the
make table query. new or temp.
but a make table query i don't think will work with a
recordset. recordsets hold the data in memory. you wanted
to put it in a table. thus the temp table idea.
the user id is an idea. but i would skip the recordset
idea and go with a runSQL for the make table query and use
the user id in the table name. don't know how to do that.
have you consider setting up a front end/back end setup.
if each user had their own local then the conflick problem
is solved and you can get back to the recordset idea/temp
table. multiple user in the same db doing the same thing
is the problem. sticky one too.
-----Original Message-----
Thanks for the pseudo code and the advise. The reason I wanted to create a
new table is that I will have several users online at the same time running
the same routine; filling the same table from the recordset. Having a new
table for each user created when they log on sounded like a way to keep
things separate. It might be better to just add another field and load it
with the user's ID. That way I can sort or query against the existing table
based on their unique records.

Any thoughts on that plan of action?

JMorrell
Click to expand...
 
When a user logs on, I know who it is. Based on that, I can get the user's
recordset, then loop through that to populate my temp table. But now that I
think about it, and since my data sits on an SQL server, I can make the temp
table local and not have to worry about it getting populated with someone
else's recordsets.

Thanks for being a logic sounding board. And I'm sure I'll be using the
'make table' code in the future.

JMorrell
 
glad to help.
good luck
-----Original Message-----
When a user logs on, I know who it is. Based on that, I can get the user's
recordset, then loop through that to populate my temp table. But now that I
think about it, and since my data sits on an SQL server, I can make the temp
table local and not have to worry about it getting populated with someone
else's recordsets.

Thanks for being a logic sounding board. And I'm sure I'll be using the
'make table' code in the future.

JMorrell


.
Click to expand...
 
Back
Top