Use of temp table

Question

Use of temp table

Mark Green

SSCrazy

Points: 2085
More actions
April 24, 2007 at 7:46 am

#153544

Please could people comment on whether or not the following bit of code looks sound?
DECLARE @Counter_temp INT
SET @Counter_temp = 1
While @Counter_temp <= (SELECT MAX(Key_m) FROM Time_definition_SPLIT) begin
  DECLARE @Instance_tag varchar
  SET @Instance_tag = (SELECT T.Instance_tag FROM Time_definition_SPLIT T WHERE T.Key_m = @Counter_temp)
  SELECT Time_class, Time_instance, Geo_class, Aggregated_area, Super_classed_disease_cat, Cause, Constraint_ref, Sex, Sex_code, Age_start, Age_end, Age_range, Sum_count = SUM(Converted_person_count)
  INTO #tmp_G_P_M_CC
  FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT
  WHERE Time_instance like @Instance_tag
  GROUP BY Time_class, Time_instance, Geo_class, Aggregated_area, Super_classed_disease_cat, Cause, Constraint_ref, Sex, Sex_code, Age_start, Age_end, Age_range

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it looks to me like you are using a loop to insert one group of data at a time, when you could do it all as a single set based query. the local variable you are declaring and looping thru is reall replacable witha join statement.

does this return the data you want?:

SELECT

Time_class,

Time_instance,

Geo_class,

Aggregated_area,

Super_classed_disease_cat,

Cause,

Constraint_ref,

Sex,

Sex_code,

Age_start,

Age_end,

Age_range,

SUM(Converted_person_count) AS Sum_count

FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT

INNER JOIN Time_definition_SPLIT ON Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT. = Time_definition_SPLIT.Instance_tag

GROUP BY

Time_class,

Time_instance,

Geo_class,

Aggregated_area,

Super_classed_disease_cat,

Cause,

Constraint_ref,

Sex, Sex_code,

Age_start,

Age_end,

Age_range

if it does, if you still need to stick it in a temp table, it is this:

SELECT

Time_class,

Time_instance,

Geo_class,

Aggregated_area,

Super_classed_disease_cat,

Cause,

Constraint_ref,

Sex,

Sex_code,

Age_start,

Age_end,

Age_range,

SUM(Converted_person_count) AS Sum_count

INTO #tmp_G_P_M_CC

FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT

INNER JOIN Time_definition_SPLIT ON

Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT. = Time_definition_SPLIT.Instance_tag

GROUP BY

Time_class,

Time_instance,

Geo_class,

Aggregated_area,

Super_classed_disease_cat,

Cause,

Constraint_ref,

Sex, Sex_code,

Age_start,

Age_end,

Age_range

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jan-155192 SSCrazy Points: 2613 More actions · Answer 2

Mark,

A bit more background info would do nicely.

Where is the END of your BEGIN block? Surely you cannot do a SELECT INTO #tmp inside a loop because it creates a new temp table and fails if it already exists. You should do inserts instead in a temp table you create before.

Pick up the books of Itzik Ben-Gan. He has a solution with a nums table that could be joined here to your Time_definition_SPLIT and that can generate all id's you need (@Counter_temp => 1-n) without the loop in one set based solution. Probably you can avoid the temp table all together.

Jan

Mark Green SSCrazy Points: 2085 More actions · Answer 3

The full context of the loop is:

DECLARE @Counter_temp INT

SET @Counter_temp = 1

While @Counter_temp <= (SELECT MAX(Key_m) FROM Time_definition_SPLIT) begin

DECLARE @Instance_tag varchar

SET @Instance_tag = (SELECT T.Instance_tag FROM Time_definition_SPLIT T WHERE T.Key_m = @Counter_temp)

SELECT Time_class, Time_instance, Geo_class, Aggregated_area, Super_classed_disease_cat, Cause, Constraint_ref, Sex, Sex_code, Age_start, Age_end, Age_range, Sum_count = SUM(Converted_person_count)

INTO #tmp_G_P_M_CC

FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT --G, Time_definition_SPLIT T

GROUP BY Time_class, Time_instance, Geo_class, Aggregated_area, Super_classed_disease_cat, Cause, Constraint_ref, Sex, Sex_code, Age_start, Age_end, Age_range

DECLARE @Min_key_A AS INT

SET @Min_key_A = (SELECT MIN(Key_m) FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT G, #tmp_G_P_M_CC t WHERE G.Time_instance = t.Time_instance )

DECLARE @Max_key_A AS INT

SET @Max_key_A = (SELECT MAX(Key_m) FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT G, #tmp_G_P_M_CC t WHERE G.Time_instance = t.Time_instance )

while @Min_key_A <= @Max_key_A begin

UPDATE Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT

SET Total_person_count = t.Sum_count

FROM Geo_postaggregated_mortality_count_with_cause_catagory_SPLIT G, #tmp_G_P_M_CC t

WHERE

G.Key_m = @Min_key_A AND

G.Time_instance = t.Time_instance AND

G.Geo_class = t.Geo_class AND

G.Aggregated_area = t.Aggregated_area AND

G.Super_classed_disease_cat = t.Super_classed_disease_cat AND

G.Cause = t.Cause AND

G.Constraint_ref = t.Constraint_ref AND

G.Sex = t.Sex AND

G.Sex_code = t.Sex_code AND

G.Age_start = t.Age_start AND

G.Age_end = t.Age_end AND

G.Age_range = t.Age_range

SET @Min_key_A = @Min_key_A +1

end

DROP TABLE #tmp_G_P_M_CC

SET @Counter_temp = @Counter_temp + 1-- I'm not convinced that this line is required

end