Checking for null columns in a table

I had a situation recently where I had to build a table that had over 150 columns, and once the script to load the table was complete and ran, I needed a quick way to check that all columns were getting populated with data. I built this script below to handle it:

CREATE PROC [util].[usp_check_for_null_cols]
@tbl_nm NVARCHAR(255)
,@schema_nm NVARCHAR(255) = ‘dbo’
AS
SET NOCOUNT ON;

DECLARE @cols AS TABLE (col_nm NVARCHAR(255), is_null BIT);

INSERT @cols (col_nm)
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tbl_nm
AND TABLE_SCHEMA = @schema_nm
;

DECLARE @curr_col NVARCHAR(255)
DECLARE cs CURSOR LOCAL FAST_FORWARD FOR SELECT col_nm FROM @cols
OPEN cs
FETCH NEXT FROM cs INTO @curr_col
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @is_null bit
DECLARE @sql NVARCHAR(MAX) = CONCAT(

IF EXISTS (SELECT 1 FROM [‘,@schema_nm,’].[‘,@tbl_nm,’] WHERE [‘,@curr_col,’] is not null)
BEGIN
SET @is_null = 0
END
ELSE
BEGIN
SET @is_null = 1
END

)
;
DECLARE @params NVARCHAR(MAX) = N’@is_null bit out’
EXEC sp_executesql @sql, @params, @is_null = @is_null OUT

UPDATE @cols SET is_null = @is_null
WHERE col_nm = @curr_col
;

RAISERROR(‘Column [%s] processed’,1,1,@curr_col) WITH NOWAIT;

FETCH NEXT FROM cs INTO @curr_col
END
CLOSE cs
DEALLOCATE cs

SELECT *
FROM @cols
WHERE is_null = 1

Checking for null columns in a table

Using Database Trigger to Enforce Naming Convention Standard

When setting up a SQL data warehouse that will be used by multiple developers, it is important that the naming of objects is consistent. This allows the database to be searchable and well organized so that new users can pick up on the data quickly and know how to navigate the database. To enforce a naming convention, you have a few options such as audit specifications, server events, and triggers.

I chose to use a database trigger for enforcing a naming convention because the maintenance i felt was easier. Below is the code. It checks based on the object if a prefix is there, and if not, it rolls back the transaction and alerts the user with an error:

 

CREATE TRIGGER [trg_naming_compliance]
ON DATABASE
FOR
DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS

AS
BEGIN

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA()

DECLARE @db_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(256)’)
DECLARE @evnt_typ VARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(250)’)
DECLARE @obj_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’)
DECLARE @obj_typ VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectType)[1]’, ‘varchar(250)’)
DECLARE @tsql_cmd VARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’)
DECLARE @usr_id VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)
DECLARE @obj_schema VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(256)’)

— Compliance Check

— Set your compliance prefixes here
DECLARE @TBL_PREFIX VARCHAR(5) = ‘tb_’
DECLARE @VIEW_PREFIX char(2) = ‘v_’
DECLARE @sp_prefix varchar(5) = ‘usp_’
DECLARE @FN_prefix varchar(5) = ‘ufn_’
DECLARE @COMP_VIOLATION_RULE VARCHAR(1000)

DECLARE @is_compliant BIT = 1

IF @evnt_typ IN (‘CREATE_TABLE’,’ALTER_TABLE’)
BEGIN
IF LEFT(@obj_nm,3) <> @TBL_PREFIX
SET @COMP_VIOLATION_RULE = ‘Table names must be prefixed with “tb_”.’
END
ELSE IF @evnt_typ IN (‘CREATE_VIEW’,’ALTER_VIEW’)
BEGIN
IF LEFT(@obj_nm,2) <> @VIEW_PREFIX
SET @COMP_VIOLATION_RULE = ‘View names must be prefixed with “v_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_PROCEDURE’,’ALTER_PROCEDURE’)
BEGIN
IF LEFT(@obj_nm,4) <> @sp_prefix
SET @COMP_VIOLATION_RULE = ‘Stored procedure names must be prefixed with “usp_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_FUNCTION’,’ALTER_FUNCTION’)
BEGIN
IF LEFT(@obj_nm,4) <> @FN_prefix
SET @COMP_VIOLATION_RULE = ‘Function names must be prefixed with “ufn_”.’
END
IF @COMP_VIOLATION_RULE IS NOT NULL
BEGIN
DECLARE @err_msg VARCHAR(8000) = CONCAT(‘** Compliance Violation ** : ‘,@COMP_VIOLATION_RULE,’ Please consult dev standards before resubmitting’)
SET @is_compliant = 1
RAISERROR(@err_msg,16,16)
ROLLBACK
END
END

Using Database Trigger to Enforce Naming Convention Standard

Converting Users from SSIS to T-SQL For ETL

I’ve spent a little over 9 years now working with T-SQL and SSIS, and I’ve lived through the following headaches known with SSIS:

  • Upgrading hundreds of packages when we upgrade SQL Server
  • Updating connection passwords when we have to refresh for security reasons
  • Dealing with user key encrypted packages and having to get those solved so that we can use and diagnose a problematic package
  • Documenting the contents of an SSIS package when a business user requests information

I could go on about listing all the other known issues with SSIS. I’m not bashing the product. It is by far one of the most fascinating and creative pieces of software I have ever seen. The internals of it that deal with memory buffers and multi-threading make it world class ETL software. But with all the headaches I had dealt with in the past, I knew there could be something easier and faster to work with.

At the end of 2013, I started building out a concept plug-n-play software for ETL that allows the end user to just use T-SQL to automate the creation and execution of an SSIS package. This would later evolve into EDIS, but at the time, I was just trying to come up with a solution that allowed me and my co-workers to build and deploy ETL faster and easier. After working through several revisions, I finally had a solution that used just parameters to build an SSIS package on the fly without the end user ever needing to open SSIS. Once I put this in front of some die-hard SSIS users, they were shocked and fascinated by how easy it was to use this new approach (in just 4 parameters, you could have a data flow package built and executed). One of them had been using SSIS for over 12 years (back when it was known as DTS from the SQL 2000 days). He said at first that he could never get used to this approach of building ETL workflows from T-SQL, but once he used the program for a couple days, he came back to me and said I needed to expand the capabilities further so that we didn’t have to create SSIS packages ever again.

Now, I feel that EDIS has matured to a product that is starting the optimization phase. It now covers data flows, FTP and S-FTP, web requests, local directory operations, zipping and unzipping files, and the capabilities continue to grow over time.

Another added benefit of EDIS is the self-documenting audit trail. For once, a DBA can go to just a single table to see all ETL that is being done on their server, and the mysterious black box of an SSIS package is now a thing of the past. This has been a huge win for large organizations that have ETL pulling from hundreds of sources. The DBA now has the ability to quickly answer the WHO, WHAT, WHEN, and WHERE.

All in all, this shift of moving developers from SSIS to EDIS is not easy. At first, most will look at the tool and say “so what? I can do this in SSIS. Why should I change?”. Once they realize how much faster they can build and update ETL, the fact that they only have to set credentials once and ditch config files, and that they only need to use SSMS to develop new ETL and not have to install SSDT/Visual Studio, that’s when they get the “ah-ha” moment, and say they don’t want to go back to SSIS ever again.

I’m not trying to use this blog as a sales pitch; this was more to just get down on paper the challenges and why I built EDIS. It has made managing my ETL team’s portfolio much easier and has empowered both small and large organizations the ability to train users quickly and easily so that they can get data into SQL Server much faster than they are used to.

 

 

Converting Users from SSIS to T-SQL For ETL

Get Real Time Stock Quote with T-SQL

 

Recently we released an update to EDIS to do a web service call and get the results back to a table in XML or JSON format. I didn’t realize at the time that this feature could evolve to do so much, but basically any website that has an OData API was fair game to try the web service against. As a test, I created a stored proc that leveraged the web service tool to get a stock price quote from yahoo finance. Below is the code. You just pass in the stock ticker symbol, and EDIS does the rest quick and easy. To try this, there is a free demo version of EDIS at http://www.sqletl.com for download.

 

CREATE proc [dbo].[usp_get_stock_price]
@stock_ticker varchar(4)
as
declare @url varchar(max) = ‘http://finance.yahoo.com/webservice/v1/symbols/’+@stock_ticker+’/quote?format=xml&#8217;

exec SSISDB.MDDataTech.usp_run_web_request
@url = @url
,@output_tbl_nm = ‘##quote_price’
;

declare @res xml = cast((select * from ##quote_price) as xml);
declare @curr_price varchar(50) = @res.value(‘(list/resources/resource/field[@name=”price”]) [1]’,’varchar(50)’)
print @curr_price

 

 

 

Get Real Time Stock Quote with T-SQL

Get notified when a user is elevated to sysadmin

I recently came across an issue where a co-worker reached out to me about their SQL Server having issues with users getting added to the sysadmin role without their knowledge. After taking to him more about it, I figured the easiest way for him to stay in the loop when users are added to this role was to setup a server trigger.

Server triggers are great for tracking logins and when server wide events occur. To get notified, you simply setup the server trigger with a call to the msdb.dbo.sp_send_dbmail proc and that’s it. Below is the code for this. This code assumes you have already configured database mail. If you haven’t, you must do this for the trigger to fire.

USE [master]
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trg_server_sec_notification
ON
ALL SERVER WITH EXECUTE AS ‘sa’
FOR DDL_SERVER_SECURITY_EVENTS

AS
BEGIN
BEGIN TRY

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA();

DECLARE @login_nm NVARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’,’NVARCHAR(255)’)
DECLARE @server_role NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/RoleName)[1]’,’NVARCHAR(255)’)
DECLARE @tsql NVARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’,’NVARCHAR(max)’)
DECLARE @login_that_was_elevated NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’NVARCHAR(255)’)
DECLARE @event NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’,’NVARCHAR(255)’)

— No notification necessary if no add event occurred
IF @event <> ‘ADD_SERVER_ROLE_MEMBER’ RETURN;

DECLARE @change_type VARCHAR(10)
IF CHARINDEX(‘ADD’,@tsql) > 0
SET @change_type = ‘added’
ELSE
SET @change_type = ‘removed’

DECLARE @msg VARCHAR(8000) = CONCAT(
‘Note: Login [‘,@login_that_was_elevated,’] has been ‘,@change_type,’ to server security group <b>[‘,@server_role,’]</b> by user [‘,@login_nm,’].<br><br>’
)
;

DECLARE @sub VARCHAR(500) = CONCAT( ‘Server Security Notification: [‘,@@SERVERNAME,’]’)

EXEC msdb.dbo.sp_send_dbmail @recipients = ‘YOUR_EMAIL@YOUR_DOMAIN.com’
,@body = @msg
,@body_format = ‘HTML’
,@subject = @sub
,@exclude_query_output = 1

END TRY
BEGIN CATCH
END CATCH

END
GO

ENABLE TRIGGER trg_server_sec_notification ON ALL SERVER
GO

 

Get notified when a user is elevated to sysadmin

Automating the Upsert

One process that any SQL developer typically gets tired of over the years is writing the update/insert (aka Upsert) statement at the bottom of a stored procedure that loads data to the target table. Wouldn’t it be great if you never had to write that again Smile ? Well, I finally got tired of it and developed a user stored procedure that takes in 5 parameters to automate upserts. The parameters are:

  1. The Target Database Name (@target_db)
  2. The Target Table Name (@target_tbl)
  3. The Target Schema Name (@target_sch) | Defaults to “dbo”
  4. The Source Table Name (@source_tbl)
  5. Delete Non Matching Data Flag (@delt_no_match) | Defaults to False “0”
    1. This parameter (when flipped to true) deletes rows out of the target table if they do not exist in the source, based on the primary key

The way this proc works is that it examines the primary key on the target table and verifies that the source table can align to the target key structure by implementing a unique covering index on the source. BOL suggests that merge statements are optimized better when a unique covering index is used on the source data set. Because of this implementation of a unique index, the proc today is constrained to only allow source tables that are temporary tables (either local or global) for security reasons. It then builds and executes a dynamic SQL merge statement by using the metadata from both tables and matches columns based on their names. But the proc also has a few extra useful features:

  1. Automatically sets the update snapshot timestamp to the current datetime if the target table has a column called “last_upd_ts”. There is no need to pre-program this column in the source
  2. If the unique index on the source table fails, an email in the try/catch block can email a user the duplicate rows to allow for quicker research of why the source failed when attempting to upsert to the target.

To better illustrate this, let’s walk through an example that loads some data to a temporary table and then runs the upsert proc. The full code for this upsert proc is attached at the end.

Creating Foundational Base Tables

First, we will create the target table that we want to use to upsert data to. In this example, I’m using my blog_posts database for this, but you can use any database you’d like.

USE blog_posts	
GO
CREATE SCHEMA UPSERT_DEMO AUTHORIZATION DBO
GO
CREATE TABLE UPSERT_DEMO.SALES_FACTS (
	 rgn_id int not null
	,fscl_wk smallint not null
	,last_upd_ts datetime2 not null
	,sls_units float
	,sls_amount float     ,primary key (rgn_id, fscl_wk)
)

Load Some Data to a temporary source table

In this section, we will create a source temporary table and load some data in it to simulate for the upsert. Notice that when we create the source temporary table, we don’t bother creating a primary key or unique index. The upsert proc will take care of this. Also notice that we have left the “last_upd_ts” column out of the source table. The upsert proc looks for this column on the target and will update the column to the timestamp that the proc was run to simplify the upsert process.

CREATE TABLE #tmp_sls (
	 rgn_id int not null
	,fscl_wk smallint not null
	,sls_units float
	,sls_amount float
)

DECLARE @now datetime2 = getdate()
INSERT #tmp_sls VALUES
	 (1, 1, @now, 50, 28.75)
	,(1, 2, @now, 26.44, 37.31)

Run the Upsert 

In this section, we will execute the upsert proc to merge the source data to the target.

EXEC sp_upsert @target_db = 'blog_posts' ,@target_tbl = 'SALES_FACTS'  

,@target_sch = 'UPSERT_DEMO'  

,@source_tbl = '#tmp_sls' ;

That’s all there is to it. This proc has saved me tons of hours when developing procedures to move data. Remember that the few key things when using this proc are:

  • The proc matches columns based on their names so make sure your source and target columns have the same name. Otherwise, this proc has no way of matching the data
  • The target table must have a primary key. Otherwise, the dynamic merge statement does not know how to align the source and target
  • The source table (for security reasons) must be a temporary table (local or global)
  • If your target table has a column called “last_upd_ts”, the uspert proc will populate it with the current GETDATE() from the SQL Server based on what rows are affected by the upsert

When my coworkers and I started using this proc, we left our existing legacy ETL alone, since a lot of that ETL had source temp table columns that weren’t the same as the target. But for newer work, we started embracing this upsert proc, and have never looked back.

**A quick word of caution**: ALWAYS test new procedures (e.g. this proc sp_upsert) in a Q/A environment first to ensure the desired results prior to using in a production environment.

I hope this helps many others to save time when writing SQL ETL that involves an upsert at the end. Feel free to let me know if you have any suggestions or questions.

And Here is the Code. Enjoy!

USE blog_posts
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Matt Martin
-- Create date: 2013-11-07
-- Description:	Executes a merge based on the target and source table passed in
/*

	Assumptions	for running this proc
		1) Target table has a primary key
		2) Source Table is a temp table (local "#" or global "##" hash)
		3) Source and Target Table columns are matched based on the name
			- the target and/or the source can have more columns than each other
				- we will only update what is both in target and source
				- we will only insert what is in source and has a corresponding column name in the target
		4) if no schema is supplied for the target table, its assumed to be dbo
*/

-- =============================================
CREATE PROCEDURE [dbo].[sp_upsert] 
	 @target_db	varchar(250)
	,@target_tbl	varchar(500)
	,@target_sch	varchar(250) = 'dbo'
	,@source_tbl	varchar(500)
	,@delt_no_match bit = 0

AS
BEGIN
	SET NOCOUNT ON;
	
	declare
		 @msg varchar(max)
		,@sql nvarchar(max)
		,@tgt_has_last_upd_ts bit
		,@tgt_tbl_full_path varchar(1000) = case 
								when left(@target_tbl,1) = '#' then 'tempdb..'+@target_tbl
								else '['+@target_db+'].['+@target_sch+'].['+@target_tbl+']' 
							end
		,@src_tbl_full_path varchar(1000) = 'tempdb..'+@source_tbl
		,@tgt_object_id int 
		,@src_object_id int
		,@crlf char(2) = char(13)+char(10)
	;

	set @tgt_object_id = object_id(@tgt_tbl_full_path,'U');
	set @src_object_id = object_id(@src_tbl_full_path,'U');

	-- ===================================================================================================================================
	-- Validate Parameters

	-- check target db
	if db_id(@target_db) is null 
		begin
			set @msg = 'Error: Target Database ['+@target_db+'] is not in the server database catalog. Stopping Proc.'
			raiserror(@msg,16,1) with nowait;
			return 16;
		end

	-- check target object
	else if @tgt_object_id is null 
		begin
			set @msg = 'Error: Target Table '+@tgt_tbl_full_path+' does not exist. Stopping Proc.'
			raiserror(@msg,16,1) with nowait;
			return 16;
		end

	-- check source object
	else if @src_object_id is null
		begin
			set @msg = 'Error: Source Table ['+@source_tbl+'] does not exist. Stopping Proc.'
			raiserror(@msg,16,1) with nowait;
			return 16;
		end

	-- check that source is a hash
	else if left(@source_tbl,1) <> '#'
		begin
			set @msg = 'Error: This upsert procedure requires that the source table is a hash/temp table prefixed with "#" or "##". '+@crlf
			+'Source table passed in ['+@source_tbl+'] does not begin with a hash. Stopping Proc.'
			raiserror(@msg,16,1) with nowait;
			return 16;
		end


	-- ======================================================================
	-- Load Table Catalog

	declare @tbl_cat as table (col_nm varchar(250), ord_pos smallint, is_pk bit, pk_ord_pos smallint, exists_in_src bit);

	set @sql = 
		'
			select c.name, c.column_id
				,case when ic.index_column_id is not null then 1 else 0 end as is_pk
				,ic.key_ordinal as pk_ord_pos
				,case when src.name is not null then 1 else 0 end as exists_in_src
			from '+@target_db+'.sys.columns as c with (nolock)
				left join '+@target_db+'.sys.indexes as i with (nolock)
					on c.object_id = i.object_id and i.is_primary_key = 1 
				left join '+@target_db+'.sys.index_columns as ic with (nolock)
					on i.object_id = ic.object_id and i.index_id = ic.index_id and c.column_id = ic.column_id
				left join (
						select name from tempdb.sys.columns with (nolock) where object_id = '+cast(@src_object_id as varchar(50))+'
					) as src
						on c.name = src.name
			where c.object_id = '+cast(@tgt_object_id as varchar(50))+' and c.is_identity = 0
		'
	;

	insert @tbl_cat (col_nm, ord_pos, is_pk, pk_ord_pos, exists_in_src)
	exec(@sql);
	
	-- ---------------------------------------------------------------------------------------
	-- Check that a primary key exists in the target

	if not exists(select 1 from @tbl_cat where is_pk = 1)
		begin
			set @msg = 
				 'Error: Target Table '+@tgt_tbl_full_path+' does not contain a primary key.'+@crlf
				+'This upsert proc requires a primary key on the target table to ensure how to join the target and source tables correctly. Stopping Proc.'
			raiserror(@msg,16,1) with nowait;

			return 16;
		end

	-- -----------------------------------------------------------------------------------------------------------------------------
	-- End Param validation     declare 
		
		 @join_txt nvarchar(max)
		,@matched_txt nvarchar(max)
		,@unmatched_insert_txt varchar(max)
		,@unmatched_values_txt varchar(max)
		,@last_upd_ts nvarchar(30)
		,@existing_ci_nm nvarchar(250)
	;

	/*
		Merge/Upsert statements have 3 basic parts:
		1 - The target and source table specification with join
		2 - the matched clause (update part)
		3 - The unmatched clause (insert part)

		The proc below assembles a merge statement concatenating these three parts together	and then executes said merge statement


	*/

	-- -------------------------------------------------------------------------------------------------
	-- Check if the target has a last_upd_ts column

	if exists(select 1 from @tbl_cat where col_nm = 'last_upd_ts') 
		begin
			set @tgt_has_last_upd_ts = 1
		end
	else
		begin
			set @tgt_has_last_upd_ts = 0
		end
	;
	-- ===================================================================================================================
	-- ===================================================================================================================
	-- [2] Build Out Merge SQL Text
		-- consists of 3 parts
			-- - i. Join Text
			-- - ii. Match Text
			-- - iii. Not Matched Text
	
	-- this spacer helps for formatting the merge text getting assembled
	declare @spacer varchar(50) = @crlf+'						';

	-- ===================================================================================================================
	-- [2i]. Join Text 

	select @join_txt = isnull(@join_txt,'')
		-- add in a break in the text to make it more readable
		+case when ROW_NUMBER() over(order by pk_ord_pos) % 3 = 0 then @spacer else '' end
		+' and tgt.['+col_nm+'] = src.['+col_nm+']' 
	from @tbl_cat
	where is_pk = 1
	;

	-- trim off the first "and "
	set @join_txt = right(@join_txt,len(@join_txt) - 5);

	-- ===================================================================================================================
	-- [2ii]. Matched Text

	select @matched_txt = 
		  isnull(@matched_txt,'') 
		-- add line breaker every three recs for merge text format
		+ case when ROW_NUMBER() over(order by t.ord_pos) % 3 = 0 then @spacer else '' end
		+', tgt.['+t.col_nm+'] = src.['+t.col_nm+']'
	from @tbl_cat as t
	where t.is_pk <> 1 and t.col_nm <> 'last_upd_ts' and exists_in_src = 1
	order by t.ord_pos
	;

	-- trim off the leading spaces & breaker
	set @matched_txt = right(@matched_txt,len(@matched_txt) -2);
	
	-- if a last_upd_ts column exists, tack it on the end of the update
	if @tgt_has_last_upd_ts = 1 begin

		set @matched_txt = @matched_txt + ', tgt.[last_upd_ts] = @now';
	end

	-- ===================================================================================================================
	-- [2iii]. Not Matched Text
	
	select @unmatched_insert_txt = 
		isnull(@unmatched_insert_txt,'')
		-- add line breaker every three recs for merge text format
		+ case when ROW_NUMBER() over(order by t.ord_pos) % 6 = 0 then @spacer else '' end
		+ ', ['+t.col_nm+']'
	from @tbl_cat as t
	where t.col_nm <> 'last_upd_ts' and exists_in_src = 1
	order by t.ord_pos
	;

	-- trim off the leading spaces & breaker
	set @unmatched_insert_txt = right(@unmatched_insert_txt,len(@unmatched_insert_txt) - 2);

	set @unmatched_values_txt = replace(@unmatched_insert_txt, '[','src.[');

	-- add last_upd_ts if exists
	if @tgt_has_last_upd_ts = 1 
		begin

			set @unmatched_insert_txt = @unmatched_insert_txt + ', [last_upd_ts]';
			set @unmatched_values_txt = @unmatched_values_txt + ', @now';

		end

	
	-- ====================================================================================================================
	-- [3] Optimize the temp table to prepare for Upsert

		-- [a] Create unique non-clustered covering index

	declare @cover_clause varchar(max) = '('+stuff(
		(
			select ', ['+t.col_nm+']'
			from @tbl_cat as t
			where t.col_nm <> 'last_upd_ts'
				and t.is_pk <> 1
				and exists_in_src = 1
			order by t.ord_pos 
		for xml path ('')),1,2,'')+')'
			
	;

	set @sql = 'create unique nonclustered index ['+(select cast(newid() as varchar(50)))+']  on ['+@source_tbl+']'	
				+' ('+stuff((select ', ['+col_nm+']' from @tbl_cat where is_pk = 1 order by pk_ord_pos for xml path ('')),1,2,'')+')';
	;
	
	-- update covering predicate
	set @sql = @sql + ' INCLUDE '+@cover_clause;

	begin try
		exec(@sql);
	end try
	begin catch

		-- If the error was a dup...load it into a global hash and email out the recs

		if charindex('The CREATE UNIQUE INDEX statement terminated because a duplicate key', error_message()) > 0
			begin

				declare @dups_sql varchar(max)

				declare @tmp_on_clause varchar(max), @gtt varchar(250), @tmp_keys varchar(max);

				set @tmp_keys = stuff((select ', ['+col_nm+']' from @tbl_cat where is_pk = 1 order by pk_ord_pos for xml path ('')),1,2,'')

				set @tmp_on_clause = 'on '+stuff((select 'and tmp.['+col_nm+'] = dups.['+col_nm+'] ' from @tbl_cat where is_pk = 1 order by pk_ord_pos for xml path ('')),1,3,'')

				set @gtt = '##'+cast(replace(newid(),'-','_') as varchar(250));


				-- isolate dups to global temp
				set @dups_sql = 
					'
						select tmp.*
						into ['+@gtt+']
						from '+@source_tbl+' as tmp
							inner join (
								select '+@tmp_keys+'
								from '+@source_tbl+'
								group by '+@tmp_keys+'
								having count(*) >=2
							) as dups
								'+@tmp_on_clause
				;

				exec(@dups_sql);

				-- just give the top 1000 recs
				set @dups_sql = 'set nocount on; select top 1000 * from ['+@gtt+']';

				set @msg = 
					 'Note: During proc usp_run_upsert, duplicate records were detected, and the proc failed.<br>'
					+'Attached is a list of duplicate records found during the upsert attempt.<br>'
					+'Notes on usp_upsert proc:<br><br>'
					+'<ul>'
						+'<li>Source Table: '+@source_tbl+'</li>'
						+'<li>Target Table: '+@tgt_tbl_full_path+'</li>'
						+'<li>Target Key Column(s): '+@tmp_keys+'</li>'
					+'</ul>'
				;
					
				-- SEND EMAIL
				exec msdb.dbo.sp_send_dbmail
					 @recipients = 'example@yourBiz.com'
					,@subject = 'Duplicates found during sp_run_upsert'
					,@body = @msg
					,@query = @dups_sql
					,@query_attachment_filename = 'dups.csv'
					,@attach_query_result_as_file = 1
					,@query_result_separator = '	'
					,@query_result_header = 1
					,@query_result_width = 32767
					,@query_result_no_padding = 1
					,@exclude_query_output = 1
					,@body_format = 'HTML'
				;

				-- drop global hash once complete
				if object_id('tempdb..'+@gtt) is not null exec('drop table '+@gtt);

			end

		-- throw it
		set @msg = 'Error attempting to create unique index on table ['+@source_tbl+'] during the usp_run_upsert proc'+@crlf
			+'Error Message: '+error_message()
		raiserror(@msg,16,16);
		return 16
	end catch



	-- =====================================================================================================================
	-- [4] Assemble Merge command

	set @sql = 
		'
			'+ case when @tgt_has_last_upd_ts = 1 then 'DECLARE @now DATETIME2 = getdate();' else '' end+'
			MERGE ['+@target_db+'].['+@target_sch+'].['+@target_tbl+'] WITH (TABLOCK) AS TGT
				USING ['+@source_tbl+'] AS SRC WITH (TABLOCK)
					ON 
						     '+@join_txt+'
				WHEN MATCHED THEN UPDATE 
					SET  
						  '+@matched_txt+'
				WHEN NOT MATCHED BY TARGET THEN
					INSERT (
						  '+@unmatched_insert_txt+')
					VALUES (
						  '+@unmatched_values_txt+')
				'+case when @delt_no_match = 1 then 'WHEN NOT MATCHED BY SOURCE THEN DELETE' else '' end +'
			;
			
		'
	;

	-- run it
	begin try
		--print @sql
		
		exec(@sql);

	end try
	begin catch
		
		-- note: we are breaking the error read-out into two parts because the raiserror msg only supports 2048 characters

		set @msg = ''
		set @msg = @msg + '<<< ERROR RUNNING USP_RUN_UPSERT >>>'+@crlf;
		set @msg = @msg + 'Below is the error readout and the merge statement that forced the error. Please review.'+@crlf;
		set @msg = @msg + 'error message -> '+ error_message()+@crlf;
		set @msg = @msg + 'error line -> '+ cast(error_line() as varchar(5))+@crlf;
		set @msg = @msg + '========================================================================================'+@crlf;

		raiserror(@msg,16,16);

		set @msg = ''
		set @msg = @msg + 'Merge statement that forced error.'+@crlf;
		set @msg = @msg + '--********************************--'+@crlf;
		set @msg = @msg + @sql;

		raiserror(@msg, 16,16);

		-- get out of dodge
		return 16

	end catch

END
Automating the Upsert

Concatenating items to a list in T-SQL

Sometimes in SQL, you need the ability to concatenate rows of data together to form delimited lists. There are two ways this can be done: Using ISNULL and Stuff/XML Path. Below are examples of both. I typically use the ISNULL version more often since it’s feels simpler for me to remember:

Using ISNULL to create a list

declare @list varchar(max) select top 10 @list = isnull(@list+';','')+FirstName from AdventureWorks2014.Person.Person print @list

-- Result:

Syed;Catherine;Kim;Kim;Kim;Hazem;Sam;Humberto;Gustavo;Pilar

Using STUFF/XML Path to create a list

declare @list_xml varchar(max); set @list_xml = stuff((select top 10 ';'+FirstName from AdventureWorks2014.Person.Person for xml path ('')),1,1,'') print @list_xml

-- Result:

Syed;Catherine;Kim;Kim;Kim;Hazem;Sam;Humberto;Gustavo;Pilar

Concatenating items to a list in T-SQL