CODEWITHSUNDEEP
sql-servert-sql
James123
James123

Reputation: 11662

how to create temp table based on column number?

How to create a temp table based on number of columns?

for example if count is 20 create a temp table with 20 columns (all are nvarchar types).

If count is 10, then create temp table with 10 columns only.

Upvotes: 1

Views: 788

Answers (1)

JNK
JNK

Reputation: 65217

If you REALLY REALLY want to do this as written, you can use dynamic SQL like below:

DECLARE @ColCount int = 20
DECLARE @Ct int = 1
DECLARE @SQL nvarchar(max) = ''

SET @SQL = 'CREATE TABLE #VarTemp('

WHILE @Ct < @ColCount+1
BEGIN
    SET @SQL += 'Col' + CAST(@Ct as nvarchar(8)) + ' nvarchar(256),'
    SET @Ct = @Ct + 1
END
SET @SQL = LEFT(@SQL, (LEN(@SQL) - 1))
SET @SQL += ')'

SELECT @SQL

Just put the outputted SQL string into another window, or if you trust it you can change the SELECT to an EXEC.

Upvotes: 3

Related Questions