i am not King of the temp tables, although i seem to use them alot.
Currently, i am extracting data into a series of temp tables to prepare a data-set to save into a standard database table. The first step is to query data from a core table into a temp table that i mess with, rework, et al. But i found that the underlying core table, for a certain market, did not total up to the extracted data – for that same market. The totals should be the same. I DID find core data that was not in my temp table, but didn’t understand why.
Turns out that i was making a join to another table, based on a market value and a pseudo-code (pcode) value. If there was, and there were, entities that didn’t have a pcode, i could not get them into the temporary table. I was prepared to select them with a case statement and give them a name, which would have solved the data total issue, but they were not being selected with the join.
My supervisor suggested a left (outer) join between the core data and the second table. The left join returns everything selectable from the core table and when possible, adds the data from the second table. He told me what to do – i set it up, ran it, and after 4 minutes casually mentioned that when i had tried the left outer join on this table, it had taken this long, and then i cancelled. Without the left join, the entire process (using a sparse dataset) returned in 10-20 seconds. We cancelled.
I mentioned that the second dataset did not have a ‘null’ pcode column in it – could we update it? No, but it was small and we COULD create a temporary table from it, adding the null data to it. We did so, added the code to the main script, ran it with a join from the temp table, it finished in seconds, and all was good. Including the data now matched.
The temp table was a select into from the original table, and a union to a ‘copy’ of the table with the new null values in it. It looked like
select * into #myTemp FROM [Product].[dbo].[out_market_def]
UNION
select distinct '', market, '' pcode, '' porder, 'all other' , mkt_name, mkt_long, 0, 0
FROM [product].[dbo].[out_market_def]
The join was to the #myTemp. We did give the blank pcodes a name (“all other”) but i wanted to take these blanks and give them a new pcode (“p99” and a name “All Other”) which i did with a case statement. this would work now since we were joining and returning rows with a pcode that was blank – the case statement says if the pcode is blank then set it to ‘p99’ else use the value:
case when c.pcode = '' then 'p99' else c.pcode end as pcode,
I would have used a similar construction to update the pcode_name as well, but that was taken care of by the ‘all other’ above.
becknspace 