Getting around having no temp tables

[Vayse]

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

 

[Guest]

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.

 

[Vayse]

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
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.