T-SQL: alter table to add a column that exists in another table

Question

I'm working with two tables in SQL Server 2008: foo and foo_mod which have the following schema:

CREATE TABLE foo
(
    [bar] DATETIME NULL ,
    [bar1] VARCHAR(20) NULL ,
    [different_column] VARCHAR(50) NOT NULL 
)

CREATE TABLE foo_mod
(
     [bar] DATETIME NULL ,
     [bar1] VARCHAR(20) NULL
)

I'd like to build a SQL script that makes the following:

For each column of foo check if that column exists in foo_mod, if not, alter foo_mod in order to add the missing column.

In this example my script will return the following:

IF COL_LENGTH('foo_mod','bar') IS NULL BEGIN
ALTER TABLE foo_mod 
  ADD bar DATETIME NULLL;
END
IF COL_LENGTH('foo_mod','bar1') IS NULL BEGIN
ALTER TABLE foo_mod 
  ADD bar1 VARCHAR(20) NULL;
END
IF COL_LENGTH('foo_mod','different_column') IS NULL BEGIN
ALTER TABLE foo_mod 
  ADD different_column VARCHAR(50) NOT NULL;
END

Now my script use a cursor to loop through the INFORMATION_SCHEMA.COLUMNS from first table:

DECLARE @column_name VARCHAR(max);
DECLARE @is_nullable VARCHAR(3);
DECLARE @data_type NVARCHAR(128);
DECLARE @default NVARCHAR(4000);
DECLARE @max_lengh INT;
DECLARE @sql VARCHAR(max);
DECLARE @output VARCHAR(max);
SET @output = '';

DECLARE col_names_cursor CURSOR
FOR
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'foo'

OPEN col_names_cursor
FETCH NEXT FROM col_names_cursor INTO @column_name, @is_nullable, @data_type,@default, @max_lengh;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'IF COL_LENGTH(''foo_mod'','''@column_name''') IS NULL BEGIN ALTER TABLE ''foo_mod'' ADD '
    SET @sql += ? -- build sql script from informations
    FETCH NEXT FROM col_names_cursor INTO @column_name, @is_nullable, @data_type,@default, @max_lengh;
END
CLOSE col_names_cursor;
DEALLOCATE col_names_cursor;

Is there an easy way to build the SQL statement from the informations given?

Thanks!

score 2 · Accepted Answer · edited May 23 '17 at 11:44

I would be inclined to use the catalog views here rather than the INFORMATION_SCHEMA, as the INFORMATION_SCHEMA does not deal with identity columns, or computed columns:

To show this I have altered your table foo slightly:

CREATE TABLE foo
(
    ID INT IDENTITY(1, 1),
    [bar] DATETIME NULL ,
    [bar1] VARCHAR(20) NULL ,
    [different_column] VARCHAR(50) NOT NULL ,
    ComputedColumn AS bar1 + different_column
)

Then you can query the catalog views to build up your statement:

SELECT  'ALTER TABLE dbo.Foo_mod ADD ' + c.Name + ' ' + 
            CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.definition
                ELSE t.Name + 
                    CASE WHEN c.is_identity = 1
                            THEN ' IDENTITY(' + CONVERT(VARCHAR(10), ic.seed_value) + ',' + CONVERT(VARCHAR(10), ic.increment_value) + ')'
                        WHEN t.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR', 'DATETIME2') 
                            THEN '(' + CONVERT(VARCHAR(10), c.max_length) + ')'
                        WHEN t.name IN ('NUMERIC', 'DECIMAL') 
                            THEN '(' + CONVERT(VARCHAR(10), c.precision) + ', ' + CONVERT(VARCHAR(10), c.scale) + ')'
                        ELSE ''
                    END
            END + ';
        GO
        '
FROM    sys.columns AS c
        INNER JOIN sys.types AS t
            ON t.system_type_id = c.system_type_id
            AND t.user_type_id = c.user_type_id
        LEFT JOIN sys.computed_columns AS cc
            ON cc.object_id = c.object_id
            AND cc.column_id = c.column_id
        LEFT JOIN sys.identity_columns AS ic
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
WHERE   c.object_id = OBJECT_ID(N'dbo.foo', 'U')
AND     NOT EXISTS
        (   SELECT  1
            FROM    sys.columns AS c2
            WHERE   c2.object_id = OBJECT_ID(N'dbo.foo_mod')
            AND     c2.name = c.name
        )
ORDER BY c.column_id;

Which generates:

ALTER TABLE dbo.Foo_mod ADD ID int IDENTITY(1,1);
GO
ALTER TABLE dbo.Foo_mod ADD different_column varchar(50);
GO
ALTER TABLE dbo.Foo_mod ADD ComputedColumn AS ([bar1]+[different_column]);
GO

Now all you need to do is capture this into a variable by concatenating the rows into a single row, and execute it:

DECLARE @SQL NVARCHAR(MAX);

SELECT  @SQL = (SELECT  'ALTER TABLE dbo.Foo_mod ADD ' + c.Name + ' ' + 
                            CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.definition
                                ELSE t.Name + 
                                    CASE WHEN c.is_identity = 1
                                            THEN ' IDENTITY(' + CONVERT(VARCHAR(10), ic.seed_value) + ',' + CONVERT(VARCHAR(10), ic.increment_value) + ')'
                                        WHEN t.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR', 'DATETIME2') 
                                            THEN '(' + CONVERT(VARCHAR(10), c.max_length) + ')'
                                        WHEN t.name IN ('NUMERIC', 'DECIMAL') 
                                            THEN '(' + CONVERT(VARCHAR(10), c.precision) + ', ' + CONVERT(VARCHAR(10), c.scale) + ')'
                                        ELSE ''
                                    END
                            END + ';
                        GO
                        '
                FROM    sys.columns AS c
                        INNER JOIN sys.types AS t
                            ON t.system_type_id = c.system_type_id
                            AND t.user_type_id = c.user_type_id
                        LEFT JOIN sys.computed_columns AS cc
                            ON cc.object_id = c.object_id
                            AND cc.column_id = c.column_id
                        LEFT JOIN sys.identity_columns AS ic
                            ON ic.object_id = c.object_id
                            AND ic.column_id = c.column_id
                WHERE   c.object_id = OBJECT_ID(N'dbo.foo', 'U')
                AND     NOT EXISTS
                        (   SELECT  1
                            FROM    sys.columns AS c2
                            WHERE   c2.object_id = OBJECT_ID(N'dbo.foo_mod')
                            AND     c2.name = c.name
                        )
                ORDER BY c.column_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

PRINT @SQL;         
EXECUTE sp_executesql @SQL;

T-SQL: alter table to add a column that exists in another table

1 Answers1