CODEWITHSUNDEEP
sql-server-2008t-sql
user829237
user829237

Reputation: 1759

Compare columns of two tables and add missing

Using SQL Server 2008 i have two tables: [Source] and [Target]

I want to check all columns of Source table and see if they exist in Target table. If they dont exist then i want to add the columns.

I have created a procedure to fix this. The only thing im struggeling with is how to set the datatypes correctly. I mean, if it is a varchar field i must specify the length. If its an int field i dont need to and so on. Is there a way to do this without creating seperate handeling of every datatype available?

Upvotes: 1

Views: 1288

Answers (2)

SQLMason
SQLMason

Reputation: 3275

This is incomplete... you'll have to handle the other datatypes that I forgot (I don't think I forgot any). However I did test it and it works.

So to answer your question, no, you have to handle the datatypes.

DECLARE @MasterTable    SYSNAME,
        @SlaveTable     SYSNAME,
        @txtSQL         VARCHAR(max)
SELECT  @MasterTable = 'orderheader',
        @SlaveTable = 'orderheader2'

DECLARE @myTable TABLE
(
    txtSQL VARCHAR(MAX)
)
INSERT INTO @myTable
( 
    [txtSQL] 
)
SELECT  'ALTER TABLE [dbo].[' + 
    @SlaveTable + 
    '] ADD [' + 
    a.[name] + 
    '] [' + 
    typ.[name] + 
    ']' + 
    CASE typ.[name]
        WHEN 'decimal' THEN '(' + CAST(a.[precision] AS VARCHAR(20)) + ',' + CAST(a.[scale] AS VARCHAR(20)) + ')'
        WHEN 'numeric' THEN '(' + CAST(a.[precision] AS VARCHAR(20)) + ',' + CAST(a.[scale] AS VARCHAR(20)) + ')'
        WHEN 'varchar' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        WHEN 'char' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        WHEN 'nvarchar' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        WHEN 'nchar' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        WHEN 'binary' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        WHEN 'varbinary' THEN '(' + CAST(a.[max_length] AS VARCHAR(20)) + ')'
        ELSE ''
    END
FROM    (
          SELECT    col.*
          FROM      sys.tables tbl
                    INNER JOIN sys.columns col
                        ON tbl.[object_id] = col.[object_id]
          WHERE     tbl.[name] = @MasterTable
        ) a
        LEFT JOIN (
                    SELECT  col.*
                    FROM    sys.tables tbl
                            INNER JOIN sys.columns col
                                ON tbl.[object_id] = col.[object_id]
                    WHERE   tbl.[name] = @SlaveTable
                  ) b
            ON a.[name] = b.[name]
        INNER JOIN sys.types typ
            ON a.[system_type_id] = typ.[system_type_id]
WHERE   b.name IS NULL

WHILE EXISTS
(
    SELECT TOP 1 1
    FROM @myTable
)
BEGIN
    SELECT TOP 1 @txtSQL = txtSQL FROM @myTable
    DELETE FROM @myTable WHERE [txtSQL] = @txtSQL
    EXEC (@txtSQL)
END

Upvotes: 2

Jonas Lincoln
Jonas Lincoln

Reputation: 9767

Is using SQL Compare from Red Gate out of the question?

Upvotes: 0

Related Questions