How To Avoid MERGE in SQL Server (2023-02-20)

SQLServerCentral Newsletter for February 20, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How To Avoid MERGE in SQL Server]( - [3 Key Areas to Drive Digital Transformation]( - [json_object and json_array Functions SQL Server 2022 Tutorial]( - [From the SQL Server Central Blogs - Rotating a Certificate Used by TDE]( - [From the SQL Server Central Blogs - Deadline for Microsoft MVP contributions is 20 March 2023]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [The Query Store Capture Mode]( The Voice of the DBA Daily Coping Tip Appreciate the good qualities of someone in your life I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours.  A Nice Holiday It's President's Day in the US today, which is a holiday for me. I don't think of it as much of a celebration, but it is a day off. So, I'm taking the day off and republishing some content. This is a US holiday, but it's Louis Riel Day in Canada, and celebrated in [quite a few other countries as well](~:text=World%20Day%20of%20Social%20Justice) for different reasons. I hope quite a few of you get the day off and don't read this until tomorrow, Feb 21. In any case, enjoy the time off when you get it. Easy to get caught up in work and overwork yourself. I am working today, but in coaching, not technology. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How To Avoid MERGE in SQL Server]( Glen Cooper from SQLServerCentral Introduction This article shows how to script a basic merge between two tables, without using MERGE. This can be handy when you're merging a pair of tables with hundreds of columns, since the script doesn't know anything about them. This technique introduces and populates some auditing columns (IS_DELETED, IS_INSERTED, IS_UPDATED, TRANSACTION_DATE) for each record, and runs fast. […] [External Article]( [3 Key Areas to Drive Digital Transformation]( Additional Articles from Redgate This article explores three key areas that need to be considered to make digital transformation occur: as a response to current changes, in preparation for future changes and to enhance the human experience. Read on to find out more! [External Article]( [json_object and json_array Functions SQL Server 2022 Tutorial]( Additional Articles from MSSQLTips.com Learn about the new functions JSON_OBJECT and JSON_ARRAY to work with JSON formatted data in SQL Server. [Blog Post]( From the SQL Server Central Blogs - [Rotating a Certificate Used by TDE]( Matthew McGiffen from Matthew McGiffen DBA In terms of encryption, Key Rotation is the process of replacing your encryption keys on a periodic basis. This is considered good practice and is required by many security... [Blog Post]( From the SQL Server Central Blogs - [Deadline for Microsoft MVP contributions is 20 March 2023]( Randolph West from Born SQL with Randolph West Since changing to the new MVP renewal model, Microsoft MVPs have had until 31 March each year to provide the list of their annual contributions. For 2023, the deadline... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Query Store Capture Mode I am using Query Store for an application my company has written. We pre-load the database with some data and have a specific set of stored procedures and queries we run to test the database. Once we've completed this, we don't want to capture any ad hoc or DBA queries that are run, but we want Query Store to continue to monitor these queries. What capture mode should we set? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) A NOT NULL Default What setting do I change to ensure when I create a table the columns have a NOT NULL default for this code: CREATE TABLE NotNullTable ( myid INT) GO Answer: SET ANSI_NULL_DFLT_OFF ON Explanation: If I set SET ANSI_NULL_DFLT_OFF to ON, this ensures NOT NULL is the default. This code shows how it works: SET ANSI_NULL_DFLT_OFF on GO CREATE TABLE NotNullTable ( myid INT) GO INSERT dbo.NotNullTable (myid) VALUES (NULL) GO DROP TABLE dbo.NotNullTable GO Ref: ANSI_NULL_DFLT_OFF - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Development and T-SQL [Plan Handle exists but NO QUERY PLAN?]( - Is this a fully legit situation in the result of the below query to have Plan Handle but NO QUERY PLAN? SELECT * FROM ( SELECT DBName = DB_NAME(ps.database_id) , ObjectName = OBJECT_NAME(ps.object_id, ps.database_id) , cached_time = ps.cached_time , last_execution_time = ps.last_execution_time , last_elapsed_time_secs = ps.last_elapsed_time / 1000000.0 , execution_count = ps.execution_count , PlanHandle = […] SQL Server 2019 - Administration [TempDB misconfiguration]( - Bit of a dilemma,  I accidentally entered the incorrect sizing of the tempdb files during the installation of a clustered instance. the installation completed but the tempdb partially created the files then ran out of space.  The issue is the SQL instal completes but failed on a few steps and now it will not start. […] SQL Server 2019 - Development [Operating System Error on Bulk Insert]( - Hello everyone, it's my first post here, and I'm no expert on SQL, so I hope you're able to help, and will forgive my ignorance, I'm keen to learn! I'm trying to import around 100 txt files into a SQL table, for processing ahead of bringing into PowerBI. I've built a query, and a layout […] [Split field to top 4]( - I need to split the column 'DiagnosisCodes' into 4 new columns 'Diagnosis1', 'Diagnosis2', 'Diagnosis3' and 'Diagnosis4' for the unique ChartProcedureId's. If they add more DiagnosisCodes, I only need the top 4. Any assistance is appreciated. SQL Server 2008 - General [Is there a way to have SQL Server be aware of a new file?]( - Every weekday we receive a file which under normal circumstances gets processed by a SQL Job that runs on a schedule at 7 AM. It's worked this way for years. This file is produced by a partner, who uploads it to a secure FTP site. However, for the last several weeks the file has either […] SQL Azure - Development [Reducing varchar(max) cols that are in auto generated indexes]( - Hi all, I inherited an app schema that is shared across 140+ different DBs [one for each client; they all have their own data but the schemas are otherwise exactly the same]. I am their tsql dev, and have been doing a lot of performance enhancements. One thing I'm running into is that one of […] General Cloud Computing Questions [Azure SQL managed Instance tiers comparison]( - Hello everyone. I have an azure general purpose managed instance, 8vCore, 1Tb storage running. The average CPU usage is 50%, with 70-75% in peak hours. Now we have a new report that need almost real-time data and since the server is heavy used, the query get a lot of concurrrency with writes tasks. Noted that […] [What are the alternatives of S3 Object Lambda?]( - Hi all! AWS has a good example of how to manipulate the requested objects from S3 and it is S3 Object Lambda which can be placed as a middleware between Amazon S3 API Consumer and S3 bucket. What are the alternatives of that in Azure, GCP, Alibaba Cloud, Oracle Cloud, RedHat, VMWare, SalesForce, DigitalOcean, etc? General [SQL Script plug-in]( - Is the SQL Script plug-in listed here no longer available? SQL Scripts in SSMS Is there any other plug-in that can be used to search SQLServerCentral scripts? Integration Services [CozyRoc REST Connection and Source]( - Any CozyRoc REST connection users out there? I'm looking for an explanation of how to set up error handling for 400 and 404 errors so that the source component won't report an error for these but just move on. I'm using some in a loop, passing an item number as a parameter and if there […] SQLServerCentral.com Website Issues [Warning prompt when writing a reply]( - When I wrote this reply and pressed submit I got an error "Are you sure you want to do that?" I could be missing the obvious but I'm not sure why the website queried my post? Possibly it's looking at language and it thought my post had a negative tone to it? Unless I'm […] SQL Server 2022 - Development [Best way to combine records across databases]( - Data which is distributed over multiple databases needs to be gathered and then queried/filtered. Currenttly a temporary table in which the records are inserted from the different databases is used however it is really slow, is there a faster way to handle it? [SQL Server Database Redunduncy]( - I am having two databases A and B Both are logging in Same DATA at the same interval of Time But in case of power failure system A it should copy missing data from system B and Vice Versa In case of power failure of System B it should copy the missing data from System […] [Return a set of rows between 2 values (multiple times)]( - Hello  I have a table, a (sample only shown) This has a set of ids in it  CREATE TABLE a (id int) INSERT INTO a (id) VALUES (1, 4, 8, 15)  I'm trying to create a set (table) that holds the id in column 1 (call it pid) and links all the […] [Update records using previous row and calculated value of current row.]( - Hi All, I am trying to update a table's column avg_gain based on previous avg_gain and current gain column for RSI calculation. I was able to implement this logic through cursor but it is time consuming, so was trying to see if there are other techniques to achieve it for having performance gains; The Formula […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -