Jack Jack - 7 months ago 17
SQL Question

Dynamic Create column in sql server with datatype

I have table

TAB_A


COL_NAME DATATYPE MAX_LENGTH
A VARCHAR 255
B INT 4
C FLOAT 8


I want to create A,B,C as column in
TAB_B
with
DATATYPE
and
MAX_LENGTH
.

The
TAB_B
columns look like this: Before

X Y Z


I want the
TAB_B
look like this: After

X Y Z A B C


with datatype.

How can I write dynamic SQL so my A,B,C,... columns will get created in existing table.

Answer

Try below,

I have created sample table like your example:

CREATE TABLE Tab_A
(
    COL_NAME       CHAR(1),
    DATATYPE       VARCHAR(100),
    MAX_LENGTH     INT
)

insert into Tab_A values('A',        'VARCHAR',   255)
insert into Tab_A values('B',        'INT' ,       4)
insert into Tab_A values('C',        'FLOAT'  ,    8)

Now I have created other table Tab_B,

CREATE TABLE Tab_B
(
    X       CHAR(1),
    Y       VARCHAR(100),
    Z     INT
)

SELECT * from Tab_B

Now, Finally I'm using dynamic query,

DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') 
       + QUOTENAME(COL_NAME) + ' ' + DATATYPE + CASE WHEN DATATYPE = 'INT' THEN '' ELSE '(' + CAST(MAX_LENGTH AS VARCHAR(10)) +')' END
FROM   (
           SELECT DISTINCT *
           FROM   Tab_A
       ) AS Courses

DECLARE @SQL VARCHAR(1000)
SELECT @SQL = 'ALTER TABLE Tab_B ADD ' + @ColumnName + ''
EXEC (@SQL)
SELECT * FROM   Tab_B