How to Improve / Shorten Merge Statements

Question

Hello Community,

This my first post to the new look Microsoft Q&A.

I hope I'm in the right place to ask questions on Transact SQL

I have the following merge statement which as you can see contains a lot of fields to be included in the 'WHEN MATCHED THEN UPDATE SET' statement

MERGE INTO NationalChainMonthlyVisits AS t  
    USING tmp_NationalChainMonthlyVisits AS s
    ON (t.fk_chains = s.fk_chains and t.local_date = s.local_date)  
    WHEN MATCHED THEN
        UPDATE SET 
            fk_chains = s.fk_chains, 
            local_date = s.local_date,
            visits = s.visits,
            visits_lower = s.visits_lower,
            visits_upper = s.visits_upper,
            visits_growth = s.visits_growth,
            scaled_visits = s.scaled_visits,
            scaled_visits_lower = s.scaled_visits_lower,
            scaled_visits_upper = s.scaled_visits_upper,
            scaled_growth = s.scaled_growth,
            visits_vs_baseline = s.visits_vs_baseline,
            visits_vs_baseline_lower = s.visits_vs_baseline_lower,
            visits_vs_baseline_upper = s.visits_vs_baseline_upper,
            scaled_visits_relative = s.scaled_visits_relative,
            scaled_growth_relative = s.scaled_growth_relative,
            percentile_rank_visits = s.percentile_rank_visits,
            percentile_rank_growth = s.percentile_rank_growth
    WHEN NOT MATCHED THEN  
        INSERT (  fk_chains,  local_date,  visits,  visits_lower,  visits_upper,  visits_growth,  scaled_visits,  scaled_visits_lower,  scaled_visits_upper,  scaled_growth,  visits_vs_baseline,  visits_vs_baseline_lower,  visits_vs_baseline_upper,  scaled_visits_relative,  scaled_growth_relative,  percentile_rank_visits,  percentile_rank_growth)  
        VALUES (s.fk_chains,s.local_date,s.visits,s.visits_lower,s.visits_upper,s.visits_growth,s.scaled_visits,s.scaled_visits_lower,s.scaled_visits_upper,s.scaled_growth,s.visits_vs_baseline,s.visits_vs_baseline_lower,s.visits_vs_baseline_upper,s.scaled_visits_relative,s.scaled_growth_relative,s.percentile_rank_visits,s.percentile_rank_growth);  

Is the a shorter way of achieving the above without writing out all the fields?

For example, in Python, I could achieve all of the above with the following few lines of code;

(deltaTable
  .alias("t")
  .merge(NationalChainMonthlyVisits.alias("s"), "t.fk_met_areas = s.fk_met_areas and t.fk_chains = s.fk_chains and t.local_date = s.local_date")
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .execute())

The above Python achieves the same as T-SQL, using the function whenMatchedUpdateAll(), and .whenNotMatchedInsertAll()

Can you let me know if there is a similar function in T-SQL?

Thanks

Answer 1

Afraid to say, there is no such shortcuts in SQL Server. It might not be handy as Python, but clean and clear what to be updated and inserted the list of columns. :)

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 2

Can you let me know if there is a similar function in T-SQL?

In T-SQL there is no such function, you have to explicit list all columns that should be updated.

Olaf

Answer 3

learn to write SQL to generate SQL , example:

DECLARE @alias1 varchar(10) = 's', @alias2 varchar(10) = 't', @Table varchar(250) = 'Employees'
, @SqlV varchar(max), @SqlI varchar(max), @SqlU varchar(max)
DECLARE @sep1 varchar(10) = CONCAT(', ',@alias1,'.')
, @sep2 varchar(20) = '
, '
;WITH a AS (
SELECT OBJECT_ID(@Table) objid, 's' alias1, 't' alias2
), b AS (
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY column_id) s1
, STRING_AGG(name, @sep1) WITHIN GROUP (ORDER BY column_id) s2
, STRING_AGG(CONCAT(name, ' = ', @alias1, '.', name) , @sep2) WITHIN GROUP (ORDER BY column_id) s3
FROM a JOIN sys.columns c ON c.object_id = a.objid AND c.column_id < 7
)
SELECT @SqlI = CONCAT('INSERT (', s1, ')')
, @SqlV = CONCAT('VALUES (', @alias1, '.', s2, ')')
, @SqlU = CONCAT('UPDATE SET ', s3)
FROM b

PRINT @SqlU
PRINT ''
PRINT @SqlI
PRINT ''
PRINT @SqlV

Answer 4

All,

I'm surprised there isn't a

WHEN MATCHED THEN UPDATE *

Or

WHEN NOT MATCHED THEN INSERT *

I would have thought MS would have allowed for this??

Answer 5

Hi @Carlton Patterson ，
Unfortunately, there is no similar function in TSQL.Although your statement has more code, but a simpler solution.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo