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)

Answer 2

Now i wrote this code:

USE sip_audi_01
DECLARE @Columname varchar(10)
DECLARE @integer int
DECLARE @query VARCHAR(MAX)
SET @integer = 0
SET @Columname = 'BYTE'
SET @query='ALTER table tbl_Archiv_BYTEsps ADD '+@Columname+' int'
WHILE @integer = 235
BEGIN
  SET @integer = @integer + 1
  SET @Columname = @Columname + CAST(@integer AS char)
  EXEC (@query)
  PRINT 'Nummer:' + CAST(@integer AS char)
END;

but nothing is happening.... where i can read the print-output in SQL-Server ManagementStudio? @Devart, sorry but your code is too difficult for me.

Answer 3

You need to use dynamic sql to execute this query.

DECLARE @query VARCHAR(MAX)

SET @query='ALTER table tbl_Archiv_BYTEsps ADD '+@Columname+' int'
EXEC (@query)

But i think it is better to review your table structure since it seems like bit of a mess to maintain 236 columns for one table.