I want to run a T-SQL script where I create a temp table that will be aggregated by a certain field of another table, call it table X. The remaining fields of this temp table will be populated by performing aggregate functions on the fields of table X. Then I would like to do a MERGE / WHEN MATCHED with my temp table on a different table (call it table Y) after I have populated the temp table.
How do I create this temp table and populate it with aggregate functions? (I have already coded the MERGE part of the problem).
to create a temp table you will can do the following:
create table #temp
(
id int,
col1 int
)
then you will write an INSERT INTO
INSERT INTO #temp
SELECT col1, sum(col2)
FROM yourTable
Once you have created your temp table you can use it in your store procedure.