How to create a temporary table without referring to permanent table i.e. without using select state-CodePudding

Is there any way to create our own temporary table or using 'With' clause to create a table with our own columns and values without using any select statement from another permanent table in a query. That temporary table I want to refer in my main query.

Please help me with this.

Thanks.

CodePudding user response：

You can certainly use a WITH however depending on the data you might have to use a lot of UNION ALL statements unless your requirement can allow to use some kind of recursive query. The below is for SQL-Server.

WITH temp_table(id, name) AS
  (
     SELECT 1, 'abc'
     UNION ALL      
     SELECT 2, 'cde'
  )

 SELECT * FROM temp_table

You can read this for more details - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15