I had a case where there was a table A, and a temporary table #A. Table A# is a temporary table and contains the data that has been imported from an Excel worksheet. The object is to update table A based on the data in table #A. The table structures are identical. In my case, I had many Excel spreadsheets, and I needed a generic way to update the permanent table with data from the temporary table.Furthermore, the update needed to consider what primary keys existed, and as such the procedure had to read the primary keys from the database dictionary. The solution is shown below. Note that in order to work, the temporary table has to exist before calling the procedure.
alter procedure dr_create_update_string (@tablename varchar(10))
as
/*-----------------------------------------------------------------------------------------------*/
DECLARE @valuelist VARCHAR(MAX);
set @valuelist='';
with a as (
select COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
), b as (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME=@tablename
), c as (
SELECT a.*, case when b.COLUMN_NAME IS NULL THEN 'No' ELSE 'Yes' END IsPrimaryKey
FROM a LEFT JOIN b
on a.COLUMN_NAME=b.COLUMN_NAME
)
select @valuelist = COALESCE(@valuelist + ',' + cast('a.'+COLUMN_NAME as varchar)+'='+cast('b.'+COLUMN_NAME as varchar) , cast('a.'+COLUMN_NAME as varchar)+'='+cast('b.'+COLUMN_NAME as varchar))
FROM c
WHERE IsPrimaryKey='No';
set @valuelist=substring(@valuelist,2,len(@valuelist)-1)
/*-----------------------------------------------------------------------------------------------*/
DECLARE @joinlist VARCHAR(MAX);
set @joinlist='';
with a as (
select COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tablename
), b as (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME=@tablename
), c as (
SELECT a.*, case when b.COLUMN_NAME IS NULL THEN 'No' ELSE 'Yes' END IsPrimaryKey
FROM a LEFT JOIN b
on a.COLUMN_NAME=b.COLUMN_NAME
)
select @joinlist = COALESCE(@joinlist + ' and ' + cast('a.'+COLUMN_NAME as varchar)+'='+cast('b.'+COLUMN_NAME as varchar) , cast('a.'+COLUMN_NAME as varchar)+'='+cast('b.'+COLUMN_NAME as varchar))
FROM c
WHERE IsPrimaryKey='Yes'
set @joinlist=substring(@joinlist,6,len(@joinlist)-1)
/*-----------------------------------------------------------------------------------------------*/
declare @sql varchar(max)
set @sql = 'update a set '+ @valuelist + ' from '+@tablename+' a JOIN #'+@tablename+' b ON ' + @joinlist
/*-----------------------------------------------------------------------------------------------*/
execute(@sql)
go
dr_create_update_string 'MM60'