Similar to how there exists a volatile table in Teradata SQL, how can I create a volatile table or something similar in Azure?

Question

Seen below is the code of how I would create a volatile table when I was coding in Teradata. I liked it because it would be the same structure and format as a normal query, but it would make a temporary table. More specifically, I like how I do not have to specify every single column and data type. I am trying to figure out how to make a similar temporary table in Azure Data Studio without having to define all of my columns. I do not have too much SQL experience and my skills are relatively basic, so any help is appreciated. Thanks!

drop table #t_table;
create volatile table #t_table as
(
SELECT
patient.ID,
patient.age,
patient.sex,
ICD.PrimaryICDCode

FROM
QP.patient

LEFT JOIN QP.ICD ON ICD.ID = patient.ID

WHERE
patient.age > 30
) with data on commit preserve rows;

Answer 1

This would be the equivalent T-SQL to generate a local temporary table without explicitly defining the structure:

DROP TABLE IF EXISTS #t_table;

SELECT
    patient.ID,
    patient.age,
    patient.sex,
    ICD.PrimaryICDCode
INTO #t_table
FROM QP.patient
LEFT JOIN QP.ICD 
    ON ICD.ID = patient.ID
WHERE patient.age > 30;

Please read up on temporary tables with attention to detail on the scope / lifecycle of them, as this probably varies from Teradata.

---

Also, please note you tagged your question with azure-data-studio which is a client tool, not a database system. Please add details on the exact database system (SQL Server, Azure SQL Managed Instance, Azure SQL Database, etc) and version you're using.