MERGE statement - a new tsql feature of SQL Server 2008 by serverku

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.

Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform. That must be ended by semicolon. Let's see the example using Merge statement.

-- Creating Database

CREATE DATABASE MergeDatabase

GO

USE MergeDatabase

GO

-- Creating tables used for merged operation

IF ( Object_id('UsingTable') > 0 )
  DROP TABLE UsingTable

GO

CREATE TABLE UsingTable
  (
     RefId INT IDENTITY(1, 1),
     name  VARCHAR(100)
  )

GO

IF ( Object_id('TargetTable') > 0 )
  DROP TABLE TargetTable

GO

CREATE TABLE TargetTable
  (
     ChildId INT,
     val     INT
  )

GO

-- Inserting records in both tables

INSERT INTO UsingTable(name)
VALUES      ('Target-1'),
            ('Target-2'),
            ('Target-3'),
            ('Target-4'),
            ('Target-5')

GO

INSERT INTO TargetTable(ChildId,val)
VALUES      (1,1),
            (2,2),
            (3,3),
            (6,6) 

GO

Let us see how Merge statement works.

1. Merge statement with WHEN MATCHED clause and updating records,>

MERGE TargetTable 
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED THEN
UPDATE set val = val + 5 ;

2. Merge statement with WHEN MATCHED clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED AND ChildId = 3 THEN
DELETE ;

3. Merge statement with WHEN NOT MATCHED BY TARGET clause and inserting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;

4. Merge statement with WHEN NOT MATCHED BY SOURCE clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

5. All together at once,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Let's see the result set of TargetTable before and after the Merge statement used.

Before Merge statement ran,

After Merge statement ran,

6. Using OUTPUT with Merge statement,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE

OUTPUT

$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;

Hope you have already started to use Merge statement.

Merge statement with TOP clause - SQL Server by serverku

A week ago, I posted for Insert, Update and Delete statement with TOP clause and Merge statement as an individual post. If you haven't read those posts, then read it before to move next. In this post I used TOP clause with DML operations and Merge statement, but both are individual posts. Let me put these two posts together here and create new one.

What is it?
It is nothing but the form of two individual posts and it is Merge statement with TOP clause.   I never used Merge statement and the TOP clause at once. Let me create the required objects in this demo or we can pick from an earlier post,

-- Creating tables used for merge operation 
IF ( Object_id('UsingTable') > 0 ) 
  DROP TABLE usingtable 

CREATE TABLE usingtable 
  ( 
     refid INT IDENTITY(1, 1), 
     name  VARCHAR(100) 
  ) 
GO

IF ( Object_id('TargetTable') > 0 ) 
  DROP TABLE targettable 

CREATE TABLE targettable 
  ( 
     childid INT, 
     val     INT 
  ) 
GO

-- Inserting records in both tables 
INSERT INTO usingtable 
            (name) 
VALUES      ('Target-1'), 
            ('Target-2'), 
            ('Target-3'), 
            ('Target-4'), 
            ('Target-5') 

GO 

INSERT INTO targettable 
            (childid, 
             val) 
VALUES      (1,1), 
            (2,2), 
            (3,3), 
            (6,6) 
GO 

Now we will run merge statement with TOP clause and also view Target table’s data before and after script run,

SELECT * 
FROM   targettable 

MERGE TOP (2) targettable 
using usingtable 
ON ( refid = childid ) 
WHEN matched AND childid = 3 THEN 
  DELETE 
WHEN matched THEN 
  UPDATE SET val = val + 5 
WHEN NOT matched BY target THEN 
  INSERT(childid, 
         val) 
  VALUES(4, 
         4) 
WHEN NOT matched BY source THEN 
  DELETE; 

SELECT * 
FROM   targettable 

GO

Merge with TOP clause

Merge without TOP clause

You can see from both images, with Merge statement with TOP clause updated only 2 rows and remaining insert and delete operation not happened which happened with Merge statement without TOP clause. Did you used both at once?

Could not find the Distributor or the distribution database for the local server - Error while posts a tracer token in Replication by serverku

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.
You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics, which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find the tsql code for same below which must be run against the publisher database,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 

Error
But after a failover when I tried the same tsql code in the switched publisher database I received an error,

"Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."

Solution
This script was running fine in the original publisher database before failover. During the investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or a server where distribution database belongs to. That means we have to do two things,

1. Configure distributor at publisher.
2. Configure publisher at distributor.

So moving ahead towards the solution and apply below solution,
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,

Use master
GO

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin 

2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,

USE master
GO

EXEC sp_adddistpublisher 
  @publisher= '<Publisher>'  -- Put publisher servername here
 ,@distribution_db= 'distribution'  -- Distribution database name
 ,@security_mode= 1 
 ,@login= 'sa' 
 ,@password= 'testpwd' 

After this workaround I was able to ran this script successfully at the publisher and scheduled for  every 5 minutes, so I can use it for replication latency alert and reports too.  Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.

Purge old data from dbo.sysmail_mailitems system table in msdb database - SQL Server by serverku

As a DBA, disk space is an important factor for daily monitoring and I encountered one issue of same for disk space. I found it based on disk space statistics report from all servers. I investigated disk space usage of all databases and found msdb system database went beyond 38 GB around. You can find the script at database size information. After database, it is needed to know where actually it was eating, which tables? I found one system table ‘dbo. sysmail_mailitems’ consumed high disk space and created space issue. Please read the post to get table size statistics for a particular database.

To make a disk space I removed some old data from dbo. sysmail_mailitems system table using a script. We can also delete data directly from this system table, but I found system stored procedure to purge old data from this table from here. Let me share with you,

1. sysmail_delete_mailitems_sp :  Permanently deletes e-mail messages from the Database Mail internal tables.
2. sysmail_delete_log_sp : Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.

And the script using these system procedures is following,

USE msdb; 
GO

DECLARE @DeleteBeforeDate DATETIME 

-- Purge data older than 30 days
SELECT @DeleteBeforeDate = Dateadd(d, -30, Getdate()) 

EXEC sysmail_delete_mailitems_sp 
  @sent_before = @DeleteBeforeDate 

EXEC sysmail_delete_log_sp 
  @logged_before = @DeleteBeforeDate 

To purge data manually, we can make it atomize with scheduled job. I do not know about setting which removed data automatically from this system table. Do you know about the same? This issue I experienced first time and I want to know about your experience if you can share here. What will be your comment to this post?

Merge statement and identity insert - SQL Server by serverku

Today I asked by my friend for merge statement and identity insert, how to insert identity column data using merge statement? I posted for the merge statement without identity insert. Please read that post first before move ahead. So I would like to publish my friend’s question and answer too. It’s nothing but simple as identity insert for single table without merge. Let me generate objects required for the demo,

IF ( Object_id('EmpList1', 'U') > 0 ) 
  DROP TABLE emplist1 

IF ( Object_id('EmpList2', 'U') > 0 ) 
  DROP TABLE emplist2 

CREATE TABLE emplist1 
  ( 
     seq1     INT NOT NULL IDENTITY(1, 1), 
     empid1   INT NOT NULL PRIMARY KEY, 
     empname1 VARCHAR(50) 
  ) 

CREATE TABLE emplist2 
  ( 
     seq2     INT NOT NULL IDENTITY(1, 1), 
     empid2   INT NOT NULL PRIMARY KEY, 
     empname2 VARCHAR(50) 
  ) 

INSERT INTO emplist1 
VALUES      (1001, 
             'Emp1001') 

INSERT INTO emplist2 
VALUES      (1001, 
             'Emp2001') 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

DELETE FROM emplist2 
WHERE  seq2 = 2 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

SELECT * 
FROM   emplist1 

SELECT * 
FROM   emplist2 

You can see in the script and the image where we have to update and insert record in table emplist1 from emplist2, where record with seq1 will be updated and record with seq3 will be inserted with an identity. So emplist1 will become a target and emplist2 will become a source for this operation. Let me put a script here for same,

SET IDENTITY_INSERT emplist1 ON 

MERGE emplist1 
USING emplist2 
ON ( empid1 = empid2 ) 
WHEN matched THEN 
  UPDATE SET empname1 = empname2 
WHEN NOT matched BY target THEN 
  INSERT(seq1, 
         empid1, 
         empname1) 
  VALUES(seq2, 
         empid2, 
         empname2) 
WHEN NOT matched BY source THEN 
  DELETE; 

SET IDENTITY_INSERT emplist1 OFF 

You can see I used IDENTITY_INSERT on top and an identity column in the code while inserting records. Now checking records after the end,

I know you know about this, but I shared this post because I never used merge statement and identity insert at once. Have you ever used?