Create Table with n Columns automatically

Question

I want to store 236 int values to sql in one row. Now i have to declare the table but i dont want to type in 236 times the columnname. The Columnnames should be BYTE001, BYTE002, ... or something other prefix as BYTE (B, INT, ...). It is possible to generate the ColumnNames automatically?

I try the following code but it does not work:

USE dbXXX
DECLARE @Columname varchar(10)
SET @Columname = 'BYTE011'
ALTER table tbl_Archiv_BYTEsps
ADD @Columname int;

i want to generate the columnname in a while loop but it seems that it is not allowed to use variables with the ADD-Command. I alwasys get the Error: Wrong Syntax in the near of '@Columname' What can i do to generate the table? Or is there another way to store my 236 integer values?

Answer 1

Try this one -

IF OBJECT_ID ('dbo.temp') IS NOT NULL
   DROP TABLE dbo.temp

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'CREATE TABLE dbo.temp (ID INT IDENTITY(1,1) PRIMARY KEY, ' + STUFF((
    SELECT ', BYTE' + RIGHT('000' + CAST(sv.number AS VARCHAR(3)), 3) + ' INT'
    FROM [master].dbo.spt_values sv
    WHERE sv.[type] = 'p'
        AND sv.number BETWEEN 1 AND 236
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

CREATE TABLE dbo.temp (ID INT IDENTITY(1,1) PRIMARY KEY, BYTE001 INT, BYTE002 INT, BYTE003 INT, BYTE004 INT, BYTE005 INT, BYTE006 INT, BYTE007 INT, BYTE008 INT, BYTE009 INT, BYTE010 INT, BYTE011 INT, BYTE012 INT, BYTE013 INT, ... , BYTE235 INT, BYTE236 INT)