INTERSECT properly handles NULLS
One hassle with doing a row by row comparison is how to deal with NULL's. Perhaps you turned off ANSI_NULLS. Alternatively, you can use the INTERSECT command. And as part of the MERGE statement, the process is straight forward. All you need is the statement:
WHEN MATCHED AND NOT EXISTS(SELECT s.* INTERSECT SELECT t.*)
Reference these for more info on INTERSECT
http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/
http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
A Declared Primary Key
MERGE requires you to tell it what column, or columns to use to match the two tables.
The script below expects the target (or destination) table to have a declared primary key. If your table does not have a declared primary key, you'll have to build out the match manually.
SSMS has a limit of 8000 characters in 'results to text'
You'll need to adjust the SSMS settings so 'Results to Text' are set to 8000. Still, if your table has many columns, the total output could exceed 8000. If it does, the output will be chunked up a bit, but still in the proper order.
Understand MERGE
Before using this script, make sure you understand just what MERGE is doing.
/*
-- SET RESULTS TO TEXT || Tools | Options | Results to Text | 8000
uses INTERSECT: for comparison, both INTERSECT EXCEPT, handle NULL as a value
, which allows proper comparison (as if ANSI_NULLS OFF, which is deprecated )
Reference these articles:
http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/
*/
set nocount on;
declare @MySourceTableView varchar(128) = '[schema].[MyInventory_table]' ;
declare @MyDestinationTable varchar(128) = 'MyOtherTable' ;
-- supports with/ or w/o schema, with/ or w/o brackets
-----------------------------------------------------------------
-- standardize object values
-----------------------------------------------------------------
declare @_source_TableView varchar(128),@_destination_Table varchar(128)
,@_source_schema varchar(128),@_destination_schema varchar(128),@_dest_object_id int ;
select @_source_TableView = OBJECT_NAME(OBJECT_ID(@MySourceTableView));
select @_source_schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@MySourceTableView));
select @_dest_object_id = OBJECT_ID(@MyDestinationTable);
select @_destination_Table = OBJECT_NAME(OBJECT_ID(@MyDestinationTable));
select @_destination_schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@MyDestinationTable));
select @_destination_schema, @_destination_Table,@_source_schema,@_source_TableView,@_dest_object_id;
-- PK from @_destination_Table;
-----------------------------------------------------------------
-- process variables varchar(max) if exceeds 8000 character truncation
-----------------------------------------------------------------
-- PK format for MERGE statement: t. = s. , ....
declare @_PKsyslist table( COLUMN_NAME nvarchar(128), ORDINAL_POSITION int ) ;
declare @colorder int = 0 ,@maxcol int = 0
,@colname varchar(128) = '' ,@colxtype int = 0
,@Values varchar(max) = '' ,@insert varchar(max) = ''
,@update varchar(max) = '' ,@openquery varchar(max) = ''
,@_PKforMergeList varchar(max) = '' ,@_PKforCIDXList varchar(max) = ''
,@MergeSection varchar(max) = '' ,@Merge1Section varchar(max) = ''
,@Merge1aSection varchar(max) = '' ,@Merge2Section varchar(max) = ''
,@SPSection_cTwo varchar(max) = '' ,@_procedure_all varchar(max) = ''
,@counter int = 0 ,@updatecolname varchar(max) = '';
declare @debugflag char(1) = 'x' ;
---------------------------------------------------------------------------------------------------------
-- Get PK for MERGE statement: t. = s. , - Separate to use ordinal position
---------------------------------------------------------------------------------------------------------
insert into @_PKsyslist (COLUMN_NAME, ORDINAL_POSITION)
select COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.TABLE_NAME = kcu.TABLE_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME and tc.CONSTRAINT_TYPE = N'PRIMARY KEY'
where kcu.TABLE_NAME = @_destination_Table and kcu.CONSTRAINT_SCHEMA = @_destination_schema ORDER BY kcu.ORDINAL_POSITION;
---------------------------------------------------------------------------------------------------------
-- assemble for MERGE statement: t. = s. , - Separate to use ordinal position
---------------------------------------------------------------------------------------------------------
Select @colorder = 0, @maxcol = 0, @_PKforMergeList = '', @_PKforCIDXList = '';
Select @colorder = min(ORDINAL_POSITION), @maxcol = max(ORDINAL_POSITION) from @_PKsyslist;
while @colorder <= @maxcol and @colorder > 0
begin
select @colname = '';
select @colname = cast(COLUMN_NAME as varchar(128)) from @_PKsyslist as l where l.ORDINAL_POSITION = @colorder;
select @colname = '[' + @colname + ']';
select @_PKforMergeList = @_PKforMergeList + 't.' + @colname + ' = s.' + @colname ;
select @_PKforCIDXList = @_PKforCIDXList + ' ' + @colname;
if @colorder != @maxcol
begin
select @_PKforMergeList = @_PKforMergeList + ' and ';
select @_PKforCIDXList = @_PKforCIDXList + ',';
select @colorder = min(ORDINAL_POSITION) from @_PKsyslist as l where l.ORDINAL_POSITION > @colorder;
end
else
select @colorder = -9;
end;
if ltrim(rtrim(@_PKforMergeList)) = ''
select @_PKforMergeList = 'looks like your destination table does not have a declared PK';
----------------------------------------------------------------------------------------
-- get max/min col info for table
----------------------------------------------------------------------------------------
Select @colorder = 0, @maxcol = 0;
Select @colorder = min(colorder), @maxcol = max(colorder) from sys.syscolumns where id = @_dest_object_id;
while @colorder <= @maxcol
begin
select @colname = '', @updatecolname = '', @colxtype = 0;
-----------------------------
-- get column name, add brackets [ ]
select @colname = '[' + [name] + ']', @colxtype = xtype from sys.syscolumns where id = @_dest_object_id and colorder = @colorder;
-- OPEN Query build; @openquery, WITHOUT TRAILING COMMA
if @colxtype in ( 167, 231)
select @openquery = @openquery + @colname ;
else
select @openquery = @openquery + @colname;
-- add to column name
if @colorder != @maxcol
begin
select @updatecolname = 't.' + @colname + ' = s.' + @colname + ',';
select @colname = @colname + ',';
select @openquery = @openquery + ',';
end
else
select @updatecolname = 't.' + @colname + ' = s.' + @colname + ' -- the end';
----------------------------------------------
-- build out the insert/values
select @insert = @insert + ' ' + @colname;
select @Values = @Values + 's.' + @colname;
select @update = @update + @updatecolname ;
-------------------------------
-- get next column order
select @colorder = min(colorder) from sys.syscolumns where id = @_dest_object_id and colorder > @colorder;
-- bump counter
set @counter = @counter + 1;
end;
-------------------------------------------------------------------------------------------------------------------------
-- MERGE build
select @MergeSection =
' MERGE [' + @_destination_schema + '].[' + @_destination_Table + '] as t' + CHAR(13) + CHAR(10)
+ ' USING [' + @_source_schema + '].[' + @_source_TableView + '] as s' + CHAR(13) + CHAR(10)
+ ' on (' + @_PKforMergeList + ') -- pk INFO HERE; FORMAT t. = s.) ' + CHAR(13) + CHAR(10)
+ CHAR(13) + CHAR(10)
+ ' WHEN MATCHED AND NOT EXISTS(SELECT s.* INTERSECT SELECT t.*) '+ CHAR(13) + CHAR(10)
+ ' THEN UPDATE SET -- format: t. = s. , ....'+ CHAR(13) + CHAR(10);
select @Merge1Section = ' ' + @update + CHAR(13) + CHAR(10) ;
select @Merge1aSection =
' WHEN NOT MATCHED BY TARGET' + CHAR(13) + CHAR(10)
+ ' THEN ' + CHAR(13) + CHAR(10)
+ ' INSERT (' + @insert + ')' + CHAR(13) + CHAR(10);
select @Merge2Section =
' VALUES ( ' + @values + ')' + CHAR(13) + CHAR(10)
+ CHAR(13) + CHAR(10)
+ ' WHEN NOT MATCHED BY SOURCE then ' + CHAR(13) + CHAR(10)
+ ' DELETE;' + CHAR(13) + CHAR(10)
+ CHAR(13) + CHAR(10);
select @SPSection_cTwo =
CHAR(13) + CHAR(10)
+ ' /* ------------------------------------------------------------------------------------------------------' + CHAR(13) + CHAR(10)
+ ' MERGE' + CHAR(13) + CHAR(10)
+ ' WHEN MATCHED AND NOT EXISTS(SELECT Source.* INTERSECT SELECT Target.*) THEN' + CHAR(13) + CHAR(10)
+ ' (requires 1:1 column match between source and target, target requires PK)' + CHAR(13) + CHAR(10)
+ ' ------------------------------------------------------------------------------------------------------ */' + CHAR(13) + CHAR(10)
+ ' ' + CHAR(13) + CHAR(10)
-- + CHAR(13) + CHAR(10);
-- chunked to support debugging
select @_procedure_all = @_procedure_all + @SPSection_cTwo;
select @_procedure_all = @_procedure_all + @MergeSection;
select @_procedure_all = @_procedure_all + @Merge1Section;
select @_procedure_all = @_procedure_all + @Merge1aSection;
select @_procedure_all = @_procedure_all + @Merge2Section;
if len(@_procedure_all) < 8000
select @_procedure_all;
else
begin
select @SPSection_cTwo;
select @MergeSection ;
select @Merge1Section ;
select @Merge1aSection ;
select @Merge2Section ;
end
No comments:
Post a Comment