Update a table that column names are unknown

I have a table but i 开发者_如何转开发dont know its columns (I only know it has "id (Uniqueidentifier) column").

I can get all columns in the table with the following query:

    DECLARE @columnNames NVARCHAR(4000) = ''

    SELECT @columnNames += '[' + COLUMN_NAME + '] '
    FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME NOT IN ('id')
    ORDER BY COLUMN_NAME

Now i want to update one record with another record's data...

How can i do this?

Thanks all...

This is how you can build a dynamic sql

DECLARE @columnNames NVARCHAR(4000) 
DECLARE @idfrom int, @idto int
DECLARE @table nvarchar(50)
SET @table = 'MY_TABLE'
SET @idfrom = 5 -- id you are copying from
SET @idto = 2   -- id you are copying to

DECLARE @sqlstring nvarchar(max)

SELECT @columnNames = coalesce(@columnNames, 'SET ') + '[' + COLUMN_NAME + ']=t2.' + '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.Columns 
WHERE TABLE_NAME = @table AND COLUMN_NAME NOT IN ('id') 
ORDER BY COLUMN_NAME 

SET @sqlstring = 'update t1 ' +stuff(@columnNames, len(@columnNames),1,'') + ' 
FROM @t t1
join @t t2 on t1.id =' + cast(@idto as varchar) + '
and t2.id = ' + cast(@idfrom as varchar)

EXEC( @sqlstring )

I think you can create a "insert into table1 ... select .. from table2" type insert string and then execute it with sp_executesql.

DECLARE @insertColumnNames NVARCHAR(4000) = ''
DECLARE @selectColumnNames NVARCHAR(4000) = ''

SELECT @insertcolumnNames += ',' + '[' + COLUMN_NAME + '] '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'TABLE_1' AND COLUMN_NAME NOT IN ('id')
ORDER BY COLUMN_NAME

SELECT @selectcolumnNames += ',' + '[' + COLUMN_NAME + '] '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'TABLE_2' AND COLUMN_NAME NOT IN ('id')
ORDER BY COLUMN_NAME

sp_executesql ('insert into TABLE_1 ('+@insertcolumnNames+') select '+ @selectcolumnNames +' from TABLE_2');

I think something similar to this can work (you will need to remove the extra , from the column strings and this is not tested at all, just an idea)