0

I have a (not normalized) legacy SQL database and am working on a task to gather code from one of several similar tables. I want to execute a lot of identical code against the #Temp table, but the tables it draws from are very dissimilar (with some columns being the same).

My code is:

IF @Variable = 'X'
BEGIN
   SELECT * INTO #Temp FROM TABLE1 WHERE Condition1 = @Condition
END

IF @Variable = 'Y'
BEGIN
   SELECT * INTO #Temp FROM TABLE2 WHERE Condition1 = @Condition
END

At this point, I execute some common code. There is quite a lot and I want to just use #Temp, not have another IF condition with the code copied in multiple times. I cannot really declare the table ahead of time (it is very wide - and they are not the same) and I cannot really normalize the DB (the legacy system is far to 'mature' and my time frame is far to small). Also, at the end of the query, the #Temp table is used for creating new rows back in the original table (so again, I cannot just declare the common parts).

At this point, I cannot make my stored proc because

There is already an object named '#Temp' in the database.

This error highlights the 2nd IF block. Adding a DROP TABLE #Temp in the IF block does not help either. So I'm having to offload the work in additional SPROCs or repeat the code in conditional statements. For readability, I don't like either of these options.

Any way to use #Temp within multiple IF blocks as above ( I really have more IF conditions, only 2 shown to give an idea of the issue).

Example SqlFiddle

sarin
  • 5,227
  • 3
  • 34
  • 63
Paul
  • 2,330
  • 2
  • 19
  • 18
  • Does `TABLE1` and `TABLE2` have the same structure (columns, datatypes)? – Lamak Apr 24 '14 at 15:10
  • @Lamak no, only a few columns are the same. I have edited the question to clarify that. – Paul Apr 24 '14 at 15:11
  • 1
    I find it weird that if the resulting `#Temp` table is so different, the rest of the code can be the same – Lamak Apr 24 '14 at 15:15
  • Legacy systems always have weird issues (you never know what is needed in the future). Imagine there are 50 columns, and 10 are the same. I want to work with those 10, and then put the rows back in the original table as new rows... with all other columns the same. – Paul Apr 24 '14 at 15:20
  • 1
    If 10 columns are the same, then you can list those columns and create the exact temp table. I don't see the issue there – Lamak Apr 24 '14 at 15:23
  • That is starting to appear to be the most readable option. However, these tables are also very deep (in addition to wide), and I am not keen on having to go to them twice (once for common values, then join again later to insert the new ones). But yes, using a declared #temp with only the common columns is looking like the solution. I will try that and see how performance is. (Still hoping for a solution where I can pull the whole row). – Paul Apr 24 '14 at 15:30
  • In addition to what @Lamak has said, it would not only solve your issue, it is a much better solution, what is the point in putting 40 columns of data into a temp table if you can't use them? – GarethD Apr 24 '14 at 15:31
  • @GarethD I need all that original data to create new records. – Paul Apr 24 '14 at 15:33
  • @Paul I mean that your code should have this structure: `CREATE TABLE #Temp()` and then your `IF` should be something like: `IF @Variable = 'X' BEGIN INSERT INTO #Temp SELECT FROM TABLE1`, `IF @Variable = 'Y' BEGIN INSERT INTO #Temp SELECT FROM TABLE2`. No need to insert twice or anything like that – Lamak Apr 24 '14 at 15:33
  • @Lamak I understand what you say. But I have to insert the entire transformed record back into the original table. So the 2 hits for seeking the record are once for the common values, then again for the rest of the row after I have the common values. Actually... that creates a new problem. At the time of insert, now I will have to write out which values come from which table (#Temp or Table1)... and effectively type out all the absurdly long lists of columns D: This problem is really deeper than it looks. – Paul Apr 24 '14 at 15:35
  • 1
    Added SQL Fiddle demonstrating issue to assist with finding the answer. See post. – sarin Apr 24 '14 at 15:49

0 Answers0