CODEWITHSUNDEEP
sqlregexsql-server-2012
DrJessop
DrJessop

Reputation: 472

How To Create a Table in SQL in which the Column Names Follow Specific Pattern

I want to create a table called BREAK_EVEN in which the column names follow the pattern B_E0, B_E1, all the way to B_E(n) where n is the number of months that I have data for.

Is there a way in MS SQL Server 2012 to use a regular expression of some sort to add all of these columns in few lines of code?

Upvotes: 0

Views: 61

Answers (1)

sniperd
sniperd

Reputation: 5274

I think you can use this to do what you want:

DECLARE @THE_SQL VARCHAR(8000)
DECLARE @N INT = 5

SET @THE_SQL = 'CREATE TABLE BREAK_EVEN  ('

WHILE @N <> 0
    BEGIN
    SET @THE_SQL = @THE_SQL + ' [B_E' + CONVERT(VARCHAR(10), @N) + '] INT,'
    SET @N = @N - 1
    END

SET @THE_SQL = @THE_SQL + ' ) ON [PRIMARY]'
PRINT @THE_SQL

You could also have it EXECUTE the SQL but I find in these cases it's a little safer to just have it print it out, and run it by hand. It's going to loop 5 times (change that number to how many ever columns you want) and write out the CREATE TABLE statement to match.

Output in the messages tab:

CREATE TABLE BREAK_EVEN  ( [B_E5] INT, [B_E4] INT, [B_E3] INT, [B_E2] INT, [B_E1] INT, ) ON [PRIMARY]

Upvotes: 1

Related Questions