在sql server中动态添加列[重复]

Question

This question already has an answer here:

• How to add dynamic column to an existing table 3 answers

I need to add column dynamically

i have syntax like this

CREATE TABLE #TEMP_TABLE(ID INT PRIMARY KEY IDENTITY,ID_KILN VARCHAR(4))
    WHILE @I <= @JML_NO
        BEGIN
                ALTER TABLE #TEMP_TABLE ADD NoUrut_@I varchar(20)
            SET @I = @I + 1
        END

</div>

Answer 1

You can use dynamic command and execute it using EXEC(...)

CREATE TABLE #TEMP_TABLE(ID INT PRIMARY KEY IDENTITY,ID_KILN VARCHAR(4))

DECLARE
  @I int=1,
  @JML_NO int=10,
  @cmd varchar(100)

WHILE @I <= @JML_NO
BEGIN
    SET @cmd=CONCAT('ALTER TABLE #TEMP_TABLE ADD NoUrut_',@I,' varchar(20)')

    EXEC(@cmd)

    SET @I += 1
END

SELECT *
FROM #TEMP_TABLE

DROP TABLE #TEMP_TABLE