This is the scenario: We have two tables, a temporary table and a permanent table. Both tables have the same structure. The data in the temporary table is newer than the data in the permanent table and we have the job of inserting only the new rows into the permanent table. We also have many instances where this is going to be used, so we want a generic way to update the permanent table. The result is shown below:
/*-----------------------------------------------------------------------------------------------*/
--Procedure: dr_create_insert_string
--Purpose: After importing a SAP report in excel format, this proc inserts all new data in the permanent table
-- based on the primary keys that are defined in the permanent table.
--Notes: Data are imported into a temporary table before the update commences
--Author: Mike Thomson
--Revision: 04.03.2014
/*-----------------------------------------------------------------------------------------------*/
alter procedure dr_create_insert_string (@tablename varchar(10))
as
DECLARE @joinlist VARCHAR(MAX);
DECLARE @wherelist VARCHAR(MAX);
set @joinlist='';
set @wherelist='';
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)),
@wherelist = COALESCE(@wherelist + ' and ' + cast('a.'+COLUMN_NAME as varchar)+'=NULL' , cast('a.'+COLUMN_NAME as varchar)+'=NULL')
FROM c
WHERE IsPrimaryKey='Yes'
set @joinlist=substring(@joinlist,6,len(@joinlist)-1)
set @wherelist=substring(@wherelist,6,len(@wherelist)-1)
declare @sql varchar(max);
set @sql = 'insert into '+@tablename+' select b.* from #'+@tablename+' b LEFT JOIN '+@tablename+' a ON ' + @joinlist + ' WHERE ' + @wherelist
execute(@sql);
go
dr_create_insert_string 'MM60'