CODEWITHSUNDEEP
sqlsql-serverdatabase
Ganesh M S
Ganesh M S

Reputation: 1063

Is there any way to create a temporary table using subquery in SQL?

For one specific problem, I want the table values as below,

number  |  number_in_words
--------------------------
   1    |        one
   2    |        two
   3    |        three
   4    |        four
   5    |        five
   6    |        six
   7    |        seven
   8    |        eight
   9    |        nine
   0    |        zero

But there is no such table in the database, and I am not supposed to create a table in DB.

Using the below subquery I got the table with only one row, is there any way I can get the entire above table using similar queries?

(select '1' as number, 'one' as number_in_words)

Upvotes: 0

Views: 1488

Answers (4)

Zorkolot
Zorkolot

Reputation: 2017

Anyone that can query the database can make a temp table. Research temp tables and you can make one exactly like that.

CREATE TABLE #temp (number int PRIMARY KEY, number_in_words varchar(10))
INSERT INTO #temp (number, number_in_words) VALUES 
 (1, 'one')
,(2, 'two')
,(3, 'three')

ect.

Then when you are done:

DROP TABLE #temp

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

you can do it like so , by prepare it inside the sub query using select from values:

(SELECT * FROM (VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four'),(5,'five')) numbertable(number,number_in_words))

Upvotes: 3

Igor
Igor

Reputation: 62213

You can also use a table variable.

DECLARE @lookup TABLE (numVal INT NOT NULL, strVal varchar(10))
INSERT INTO @lookup(numVal, strVal) VALUES (1, 'one'), (2, 'two'), (3, 'three') --etc

-- and then use the value similar to a normal table using joins or whatever
select l.strVal --,  other values
from yourTable as yt INNER JOIN @lookup as l ON yt.numericValue = l.numVal

Upvotes: 1

Athi
Athi

Reputation: 391

I dont think you can have DDL(create table) within DML, Try check "WITH" common table expression, behind the scene, WITH clause will create a temporary table and you can reference it in your sub query.

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

Upvotes: 1

Related Questions