SQL Insert into table variable as alias

Question

New to the SQL Alert!

I have declared a table variable and doing a standard

Insert into @table
(
    columns
)
select (acquired data result)

I am planning to do a yearly data comparison by month (e.g compare data for April of this and last years). Since I don't want to create a separate table with the same structure unless I absolutely have to, here is my question:

Is it possible to insert different datasets into the same temp table as aliases to distinguish between the two?

Like, e.g.

(Insert into @table
(
    columns
)
select (acquired data 1 result)) as alias1

and

(Insert into @table
(
    columns
)
select (acquired data 2 result)) as alias2

note: that's a table variable, not a temp table. temp tables are `create table #temptablename ...` -- Also, the short answer to your question is: not like that. The alias as you have it is just an alias for the source you are selecting from. — SqlZim, Apr 27 '17 at 20:45
You can use your INSERT statement to put any columns into any others as long as they have compatible datatypes. If your data has the same structure each year, then why not just add some sort of identifier to your table variable like a timestamp or a daterange so that you can know where that data came from and filter accurately. You can get data from just April by using `DATEPART(MM, [myDateField]) = 4` for example. — EMUEVIL, Apr 27 '17 at 20:46
@SqlZim Even though I encased the whose insert statement into brackets? — Vadzim Savenok, Apr 27 '17 at 20:51

score 1 · Accepted Answer · edited May 23 '17 at 12:17

For your stated purpose of comparing a month's data to the same month in the prior year ... you might be able to use common table expressions instead of table variables.

e.g.

;with PriorYear as (
  select ...
  from ...
  where ...
)
, CurrentYear as (
  select ...
  from ...
  where ...
)
select ...
from PriorYear 
  inner join CurrentYear 
    on ...

There may be many better ways than this, it just depends on what sort of comparisons and operations you are doing.

And if you have more than one row in a table variable, you would probably be better off using actual temporary tables instead of table variables.

Reference:

EMUEVIL · Answer 2 · 2017-04-27T21:10:05.267

If you want to compare a month of data against all past year's months, all you need is some sort of timestamp field that you can group by year and filter by month. You can use the following to fit your example.

SELECT COUNT(*) AS [SomeAggregateFunction]
    , DATEPART(YEAR, [myTimestamp]) AS [Year]
FROM [myTable]
WHERE DATEPART(MM, [myTimestamp]) = 12
GROUP BY DATEPART(YEAR, [myTimestamp])

This will only display results from "December" and group the information by year. You can use any sort of aggregate functions to get useful information like "SUM()" or "MAX()" instead of my "COUNT(*)". A good example of when this would be useful is determining the amount of Orders that were placed each year in a certain month or something. Or if you wanted to get a SUM of the amount of sales made each december. Just edit it as you see fit. If you need help, feel free to comment.

@VadzimSavenok as far as table variables go. I do not believe you need to make one if all you want to do is compare the data that already exists in the table. If you want to perform other more complex functions on it then maybe, but just making a report on monthly usage across the years can be done without creating any sort of table variable or temp table. — EMUEVIL, Apr 27 '17 at 21:15

SQL Insert into table variable as alias

2 Answers2