自动创建具有n列的表 [英] Create Table with n Columns automatically

问题描述

我想在一行中将236个int值存储到sql中。现在，我必须声明该表，但我不想键入236次columnname。列名应为BYTE001，BYTE002，...或其他前缀BYTE（B，INT，...）。
可以自动生成ColumnName吗？

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;

我想在while循环中生成列名，但似乎不允许使用变量使用ADD-Command。我总是收到 @Columname
附近的错误：语法错误我该怎么办才能生成表？还是有另一种方式来存储我的236个整数值？

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?

推荐答案

尝试这个-

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

输出-

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)

这篇关于自动创建具有n列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！