CODEWITHSUNDEEP
sql-servert-sql
mholmes
mholmes

Reputation: 177

How To: Auto Name Column when building a table

I seen some answers that seemed close to my solution but they used code and I'd prefer to do this in SQL if I can. I know roughly what's needed but I have never seen anyone do this before in SQL. I'm building a table but I don't want to setup each column name by hand. The structure will be something similar to this:

ID - BIGINT - Primary Key - Seed Increment 1 start at 1
Email - NVARCHAR(500) do not allow null
Board_Name - NVARCHAR(100) - Do not allow null 
Tile_1 - BIT - Do Not Allow Null
Tile_2 - BIT - Do Not Allow Null
Tile_3 - BIT - Do Not Allow Null
Tile_4 - BIT - Do Not Allow Null
Tile_5 - BIT - Do Not Allow Null
Tile_6 - BIT - Do Not Allow Null
Tile_7 - BIT - Do Not Allow Null
Tile_8 - BIT - Do Not Allow Null
Tile_9 - BIT - Do Not Allow Null
Tile_10 - BIT - Do Not Allow Null

The table "Tile_X" I need to do this 348 times. So obviously I don't want to do that 348 times. 

 DECLARE @X INT

 SET @X = 1

 WHILE (@X <=348)
 BEGIN
     PRINT @X
     SET @X = @X + 1

     ALTER TABLE [dbo].[Game_Board]
     ADD 'Tile_' + @X BIT NOT NULL;
 END
 GO

Gives error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Tile_'.

Update: I already built the table but I did not add the Tile columns yet. So I need to figure out how to fix this loop to get it to add them.

Update: Also tried this:

DECLARE @X INT
DECLARE @Column NVARCHAR(100)

SET @X = 1
SET @Column = 'Tile_' + @X

WHILE (@X <=348)
BEGIN
    PRINT @X    

    ALTER TABLE [dbo].[Game_Board]
    ADD @Column BIT NOT NULL;

    SET @X = @X + 1
    SET @Column = 'Tile_' + @X
END
GO

Get error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@Column'.

Upvotes: 0

Views: 577

Answers (1)

user11380812
user11380812

Reputation:

You should use dynamic T-SQL as shown in the code below 

DECLARE @X INT;
DECLARE @Column NVARCHAR(100);
SET @X = 1;
SET @Column = 'Tile_' + CAST(@X AS NVARCHAR(3));
DECLARE @tsql AS NVARCHAR(MAX)= '';
WHILE(@X <= 348)
    BEGIN
        SET @tsql = @tsql + ' ALTER TABLE [dbo].[Game_Board] '
        SET @tsql = @tsql + ' ADD ' + @Column + ' BIT NOT NULL;';
        SET @X = @X + 1;
        SET @Column = 'Tile_' + CAST(@X AS NVARCHAR(3));
    END;
--PRINT @tsql
EXEC sys.sp_executesql 
    @tsql;
GO

See also sp_executesql (Transact-SQL)

Upvotes: 1

Related Questions