MERGE without specifying column names in SQL Server 2008

Question

I was looking at the MERGE command which seems cool but still it requires the columns to be specified. I'm looking for something like:

MERGE INTO target AS t
USING source AS s          
WHEN MATCHED THEN
    UPDATE SET
    [all t.fields = s.fields]
WHEN NOT MATCHED THEN 
      INSERT ([all fields])
      VALUES ([all s.fields])

Is it possible?

Answer 1

Not everything you wanted, but partially:

WHEN NOT MATCHED THEN
INSERT([all fields])
VALUES (field1, field2, ...)

(The values list has to be complete, and match the order of the fields in your table's definition.)

Answer 2

I'm lazy... this is a cheap proc I wrote that will spit out a general MERGE command for a table. It queries information_schema.columns for column names. I ripped out my source database name - so, you have to update the proc to work with your database (look for @SourceDB... I said it was cheap.) Anyway, I know others could write it much better - it served my purpose well. (It makes a couple assumptions that you could put logic in to handle - namely turning IDENTITY_INSERT OFF - even when a table doesn't have identity columns.) It updates the table in your current context. It was written against sql server 2008 to sync up some tables. Use at your own risk, of course.

    CREATE PROCEDURE [dbo].[GenerateMergeSQL]
    @TableName varchar(100)
AS
BEGIN
    SET NOCOUNT ON 

    declare @sql varchar(5000),@SourceInsertColumns varchar(5000),@DestInsertColumns varchar(5000),@UpdateClause varchar(5000)
    declare @ColumnName varchar(100), @identityColName varchar(100)
    declare @IsIdentity int,@IsComputed int, @Data_Type varchar(50)

    declare @SourceDB as varchar(200)


    -- source/dest i.e. 'instance.catalog.owner.' - table names will be appended to this
    -- the destination is your current db context
    set @SourceDB = '[mylinkedserver].catalog.myDBOwner.'

    set @sql = ''
    set @SourceInsertColumns  = ''
    set @DestInsertColumns  = ''
    set @UpdateClause  = ''
    set @ColumnName  = ''
    set @isIdentity = 0
    set @IsComputed = 0
    set @identityColName  = ''
    set @Data_Type  = ''


DECLARE @ColNames CURSOR
SET @ColNames = CURSOR FOR 
    select column_name, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IsIdentity ,
        COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsComputed') as IsComputed , DATA_TYPE
    from information_schema.columns where table_name = @TableName order by ordinal_position

OPEN @ColNames
FETCH NEXT FROM @ColNames INTO @ColumnName, @isIdentity, @IsComputed, @DATA_TYPE

WHILE @@FETCH_STATUS = 0
    BEGIN
        if @IsComputed = 0 and @DATA_TYPE <> 'timestamp'
        BEGIN
            set @SourceInsertColumns = @SourceInsertColumns + 
                case when @SourceInsertColumns = '' THEN '' ELSE ',' end +
                'S.' + @ColumnName

            set @DestInsertColumns = @DestInsertColumns + 
                case when @DestInsertColumns = '' THEN '' ELSE ',' end +
                @ColumnName

            if @isIdentity = 0
            BEGIN
                set @UpdateClause = @UpdateClause + 
                case when @UpdateClause = '' THEN '' ELSE ',' end
                 + @ColumnName + ' = ' + 'S.' + @ColumnName + char(10)
            END

            if @isIdentity = 1 set @identityColName = @ColumnName
        END

        FETCH NEXT FROM @ColNames INTO @ColumnName, @isIdentity, @IsComputed, @DATA_TYPE
    END

CLOSE @ColNames
DEALLOCATE @ColNames

    SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON;
            MERGE ' + @TableName + ' AS D
                USING ' + @SourceDB + @TableName + ' AS S
                ON (D.' + @identityColName + ' = S.' + @identityColName + ')
            WHEN NOT MATCHED BY TARGET
                THEN INSERT(' + @DestInsertColumns + ') 
                VALUES(' + @SourceInsertColumns + ')
            WHEN MATCHED 
                THEN UPDATE SET 
                    ' + @UpdateClause + '
            WHEN NOT MATCHED BY SOURCE
                THEN DELETE
            OUTPUT $action, Inserted.*, Deleted.*;
            SET IDENTITY_INSERT ' + @TableName + ' OFF'

    Print @SQL

END

Answer 3

Simple alternative to merge without naming any fields or having to update statement whenever table design changes. This is uni-directional from source to target, but it can be made bi-directional. Only acts on changed records, so it is very fast even with linked servers on slower connection.

--Two statement run as transaction batch
DELETE
    C
FROM
    productschina C
    JOIN 
    (select * from productschina c except select * from productsus) z
    on c.productid=z.productid

INSERT into productschina select * from productsus except select * from productschina

Here is code to setup tables to test above:

--Create a target table
--drop table ProductsUS
CREATE TABLE ProductsUS
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO
--Insert records into target table
INSERT INTO ProductsUS
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
--drop table productschina
CREATE TABLE ProductsChina
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO
--Insert records into source table
INSERT INTO ProductsChina
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO
SELECT * FROM ProductsUS
SELECT * FROM ProductsChina
GO