T-sql manually create table in a nested statement?

The Sandbox Software/Technology
1

In the T-sql environment i operate in, i’m unable to create tables. But i can create nested statements ad infinitum, which is what i go with because i have to.

If i had write table permission i could do something like the following

create table qtr_start_end
( QTR_START datetime, QTR_END datetime );
insert into qtr_start_end (qtr_start, qtr_end)
values
(‘06/30/2021 11:59:59’ , ‘09/30/2021 11:59:59’ ) ,
(‘09/30/2021 11:59:59’ , ‘12/31/2021 11:59:59’ ) ;

and that should create a table with two records. My question is, how can I do that in a nested statement?

is there a way to write something like

with qtr_start_end as
(
create temp (name1 type1, name2 type2
insert into temp (date1start, date1end) , (date2start, date2end)
)

?

2

(select
‘06/30/2021 11:59:59’ qtr_start
, ‘09/30/2021 11:59:59’ qtr_end
union
select
‘09/30/2021 11:59:59’
, ‘12/31/2021 11:59:59’
) qtr_start_end

1 Like
3

Does your permissions limitation extend to temporary tables?

1 Like
4

Correct. I have zero write access in any way. I’m not sure where a temp table would be stored. In SAS, and when i used sql server, i could create temp tables, but when i closed the program the temp tables would be deleted. I have no such ability presently.

5

This is basically the winner, though i made a slight tweak to it to be like

with qtr_start_end as
(select
‘06/30/2021 11:59:59’ qtr_start
, ‘09/30/2021 11:59:59’ qtr_end
union
select
‘09/30/2021 11:59:59’
, ‘12/31/2021 11:59:59’
)
select * from qtr_start_end

I just need to test that qtr_start and qtr_end will function like the datetimes i need them to be.

Thanks!

6

Glad that seems to be working for you.

By temp tables I meant using a # in front of the name you use in the query. That is “stored” only while the query is running. I don’t know all the technical stuff about it (don’t use them myself) so that’s why I asked.

What about declaring variables, can you do that?

I do wonder slightly about why you are using a table for two items, and using two fields for start/end. I will pretend that datetime is important (I only use dates) but that doesn’t really make a difference for what I am proposing.

Are you trying to bucket records into 1q and 2q for instance? Why don’t you use a CASE function in your SELECT statement? Also isn’t the end of one period the start of the next? Use > end dt 1st and <= end dt 2nd to put in 2nd period?

7

The actual table will have many more quarters. I try to simplify for my examples on line. Then expand what would apply to something small, to something much larger for my actual work purposes. The datetime is important because the field i’m joining on is datetime, and it’s the easiest to just have consistent dataype joins/interactions in my experience.

8

I would consider using a function to create a “Quarter variable” rather than a join. Q = 4*YEAR(datetime field) + floor((MONTH(datetime field)+2)/3).
It seems easier than creating a table or CASE statement with a lot of parts.

ETA: this approach works best when the end points have a easily defined function, like simple calendar quarter above. One system at my old company was a weekly system, so picking the right business monday/friday was more complicated than the example above.

9

There are no permissions required to write temp tables, specifically tables written to tempdb. Internally, the server will need the ability to write your queries out to disk if necessary, so it doesn’t make sense to have that restriction.

Try inserting or writing to a table that starts with “#” as mentioned earlier. For example, you can create table or select into a table called #mytemptable

You can get away with using CTEs, but in more complicated situations it can be better to write out to a temp table.