Created
March 23, 2015 19:08
-
-
Save mtherien/f5f8b833ae4297a40c85 to your computer and use it in GitHub Desktop.
Creates a merge statement to get data from one table and merge it into another database table.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| declare @sourceServer varchar(100) | |
| declare @destinationTable varchar(100) | |
| declare @destinationTableSchema varchar(100) | |
| declare @sourceQuery nvarchar(max) | |
| set @sourceQuery = 'select-statement-that-gets-data' | |
| set @destinationTableSchema = 'dbo' | |
| set @destinationTable = 'destination-table-name' -- set table name here | |
| -- Begin work | |
| SET NOCOUNT ON | |
| declare @sql table(s nvarchar(max), id int identity) | |
| --Determine if table has an identity column | |
| DECLARE @destinationTableHasIdentity bit | |
| SELECT @destinationTableHasIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') | |
| FROM INFORMATION_SCHEMA.TABLES | |
| WHERE TABLE_NAME = @destinationTable and TABLE_SCHEMA = @destinationTableSchema | |
| insert into @sql(s) values ('Print ''Merging table [' + @destinationTableSchema + '].[' + @destinationTable + ']...''') | |
| if @destinationTableHasIdentity = 1 | |
| BEGIN | |
| insert into @sql(s) values ('SET IDENTITY_INSERT [' + @destinationTableSchema + '].[' + @destinationTable + '] ON ') | |
| END | |
| -- primary key | |
| declare @pkname varchar(100) | |
| select @pkname = constraint_name from information_schema.table_constraints | |
| where table_name = @destinationTable and TABLE_SCHEMA = @destinationTableSchema and constraint_type='PRIMARY KEY' | |
| insert into @sql(s) values( ''); | |
| -- Begin MERGE statement | |
| INSERT into @sql(s) values ('MERGE INTO [' + @destinationTableSchema + '].[' + @destinationTable + '] destination ') | |
| INSERT INTO @sql(s) values ('USING (' + @sourceQuery + ' ) as source '); | |
| INSERT INTO @sql(s) values ('ON '); | |
| -- use primary keys for ON | |
| insert into @sql(s) | |
| select ' destination.[' + COLUMN_NAME + '] = source.[' + COLUMN_NAME + '] and' | |
| FROM information_schema.key_column_usage | |
| where constraint_name = @pkname | |
| order by ordinal_position | |
| update @sql set s=left(s,len(s)-3) where id=@@identity and right(s,3)='and' | |
| INSERT into @sql(s) values ('WHEN MATCHED') | |
| INSERT INTO @sql(s) values ( 'THEN ') | |
| INSERT INTO @sql(s) values ( ' UPDATE SET') | |
| insert into @sql(s) | |
| select case when EXISTS(select * | |
| FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN | |
| INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME | |
| WHERE t.table_name=@destinationTable and | |
| t.TABLE_SCHEMA = @destinationTableSchema and | |
| c.COLUMN_NAME=sc.COLUMN_NAME and | |
| COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1) | |
| then '' ELSE | |
| ' destination.['+column_name+'] = source.[' + column_name + '],' | |
| end | |
| from information_schema.columns sc where table_name = @destinationTable and TABLE_SCHEMA = @destinationTableSchema | |
| order by ordinal_position | |
| update @sql set s=left(s,len(s)-1) where id=@@identity and right(s,1) = ',' | |
| INSERT into @sql(s) values ('WHEN NOT MATCHED') | |
| INSERT into @sql(s) values ('THEN') | |
| INSERT INTO @sql(s) values ( ' INSERT (') | |
| insert into @sql(s) | |
| select | |
| ' [' + column_name+'],' | |
| from information_schema.columns where table_name = @destinationTable and TABLE_SCHEMA = @destinationTableSchema | |
| order by ordinal_position | |
| update @sql set s=left(s,len(s)-1) where id=@@identity and right(s,1) = ',' | |
| INSERT into @sql(s) values ( ' )'); | |
| INSERT INTO @sql(s) values ( ' VALUES (') | |
| insert into @sql(s) | |
| select | |
| ' source.[' + column_name+'],' | |
| from information_schema.columns where table_name = @destinationTable and TABLE_SCHEMA = @destinationTableSchema | |
| order by ordinal_position | |
| update @sql set s=left(s,len(s)-1) where id=@@identity and right(s,1) = ',' | |
| INSERT into @sql(s) values ( ' );'); | |
| -- INSERT ([RecordOwnerTypeId], [Description]) | |
| -- VALUES (seed.[RecordOwnerTypeID], seed.[Description]); | |
| if @destinationTableHasIdentity = 1 | |
| BEGIN | |
| insert into @sql(s) values ('') | |
| insert into @sql(s) values ('SET IDENTITY_INSERT [' + @destinationTableSchema + '].[' + @destinationTable + '] OFF ') | |
| insert into @sql(s) values ('') | |
| END | |
| insert into @sql(s) values ('Print ''...done merging table [' + @destinationTableSchema + '].[' + @destinationTable + ']''') | |
| -- result! | |
| declare @currentResultId int | |
| declare @maxResultId int | |
| declare @outputCommand nvarchar(max) | |
| SELECT @currentResultId = MIN(id) from @sql | |
| SELECT @maxResultId = MAX(id) from @sql | |
| WHILE (@currentResultId <= @maxResultId) | |
| BEGIN | |
| SELECT @outputCommand = s FROM @sql where id = @currentResultId | |
| print @outputCommand | |
| set @currentResultId = @currentResultId + 1 | |
| END | |
| --select s from @sql order by id | |
| SET NOCOUNT OFF | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment