Getting around having no temp tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vayse - 20 Jul 2006 11:03 GMT

hmm ,this lack of temp tables makes things a bit trickier!

There's 3 tables I'm working with. I'll just give the simplified versions
here
Renewal - RenewID, RenewYear, RenewClientID
RenewProcess: ProcessID, RP_RenewID, RP_Stage
Stages: StageCode, StageDesc

Stages is just a list of Stages that the Process can go through.
So, come a new year, create Renewals via SQL. What I used to was create a
table, TempRenewals. Then use that to update the Renewals table.
Then append to the RenewProcess table as follows:
INSERT INTO RenewalProcess ( RP_RenewID, RP_Stage )
SELECT TempRenewals.RenewId, Stages.StageCode
FROM TempRenewals, Stages;

So for each Renewal, create an RenewProcess for every stage.

How do I do this in ADP?

Thanks
Diarmuid

Reply to this Message

Baz - 25 Jul 2006 09:43 GMT

Presumably you have a make-table or append query that creates or populates
your "temp" table, so why not (i) change that query to be an ordinary SELECT
query and (ii) use it as a subquery instead of the temp table e.g.

INSERT INTO RenewalProcess ( RP_RenewID, RP_Stage )
SELECT TempRenewals.RenewId, Stages.StageCode
FROM (SELECT somedata FROM somewhere) AS TempRenewals, Stages;

Creating temporary tables is rarely necessary, but if absolutely essential
you can create true temporary tables in SQL Server (unlike in Access). You
could either do your whole series of operations in a stored procedure, or
submit all the statements to SQL Server as one SQL batch.

Try looking up "temporary tables" in Books Online.

> hmm ,this lack of temp tables makes things a bit trickier!
>
[quoted text clipped - 18 lines]
> Thanks
> Diarmuid

Reply to this Message

Vayse - 27 Jul 2006 12:44 GMT

Thanks, I'll try that out.
Diarmuid

> Presumably you have a make-table or append query that creates or populates
> your "temp" table, so why not (i) change that query to be an ordinary
[quoted text clipped - 35 lines]
>> Thanks
>> Diarmuid

Reply to this Message