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.
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
tinyint,smallint,int,bigint,realSELECT * FROM sys.types to get all data typesThis statement will provide the desired query:
DECLARE @TAB_A SYSNAME = 'dbo.Tab_A'
, @TAB_B SYSNAME = 'dbo.Tab_B'
DECLARE @dynsql NVARCHAR(MAX)
SELECT @dynsql = COALESCE(@dynsql,'') + qry
FROM (
SELECT
'ALTER TABLE ' + @TAB_B + ' ADD COLUMN '
+ COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ' ' WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(MAX) ' ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ') ' END
+ CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
+ CASE WHEN COLUMN_DEFAULT IS NULL THEN '' ELSE ' DEFAULT ' + COLUMN_DEFAULT END
+ '; ' AS qry
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = PARSENAME(@TAB_A,1)
AND TABLE_SCHEMA = COALESCE(PARSENAME(@TAB_A,2),'dbo')
) r
SELECT @dynsql
Now you can also replace the
SELECT @dynsql
with
EXEC(@dynsql)
but be aware that your table A will be altered when executing.
