0

I have two tables:

1) is a list of all parameter-ids and the info to which set of parameters the parameter-id belongs

2) is data that includes some of the parameter-ids, and some additional data such as timestamp and values.

I'm designing a data-warehouse-like system. But instead of a summary table where i store precalculated values (that doesn't really make sense in my case) i try to decrease the amount of data the different reporting-scripts have to look through to get their results.

I want to transfer every row that is in table2 into a table for each set of parameters so that in the end i have "summary tables", one for each set of parameters. Which parameter belongs to which set is saved in table1.

Is there a faster way than to loop over every entry from table1, get @param_id = ... and @tablename = ... and do a INSERT INTO @tablename SELECT * FROM table2 WHERE parameter_id = @param_id? I read that a "Set based approach" would be faster (and better) than the procedural approach, but I don't quite get how that would work in my case.

Any help is appreciated!

Flo Win
  • 154
  • 10

1 Answers1

0

Don't do it. Your 3rd table would be redundant with the original two tables. Instead do a JOIN between the two tables whenever you need pieces from both.

SELECT t1.foo, t2.bar
    FROM t1
    JOIN t2 ON t1.x = t2.x
    WHERE ...;
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Maybe a bit more information: I am processing the data after staging it and i want to sort the data according to sets, so the script running on a dataset on a cyclic basis doesn't have to go through 100M rows of data every x minutes. I have the configuration for which parameter belongs to which set in table1, and all the incoming data in table2. Is that still a "Don't do it"? – Flo Win Mar 24 '15 at 07:24
  • Is this like building a Summary Table for a Data Warehouse example? That is, t1 and t2 are temporary and are thrown away, and you keep only t3? If so, then my answer is irrelevant. If so, Edit your question to point that direction; then I will Remove or otherwise deflate my Answer. – Rick James Mar 24 '15 at 17:31