.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Merge Statement in SQL Server.

Posted By:Mahesh       Posted Date: March 29, 2012    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

This article shows how merge query is useful while updating database table in single query.
 

Merge Statement in SQL.

This is very interesting & powerful query provided by SQL Server 2008, using this query we can add/update/delete multiple records in a table using single query without creating multiple insert/update/delete queries.

1) We can also achive same functionality in SQL 2005 to as,
Suppose we are having addresses table which contains multiple addresses for employee then we can make

drop table #addresses

create table #addresses (ADDRESS_ID int,
LINE1 varchar(50),
LINE2 varchar(50),
CITY varchar(50),
STATE_OR_PROVINCE varchar(50),
COUNTRY varchar(3)
)

insert into #addresses 
select ADDRESS_ID, isnull(LINE1,'line1merge'), isnull(LINE2,'line2merge'), isnull(CITY,'CITY'), STATE_OR_PROVINCE, COUNTRY from addresses  where ADDRESS_ID in (535,536)

insert into #addresses  values( 538, 'test538', 'test5638', 'te', 't', '38' )


DECLARE @ADDRESS_IDS TABLE(ID INT)
UPDATE addresses SET LINE1 = vad.LINE1,
      LINE2 = vad.LINE2,
      CITY = vad.CITY,
      STATE_OR_PROVINCE = vad.STATE_OR_PROVINCE,
      COUNTRY = vad.COUNTRY
OUTPUT inserted.ADDRESS_ID INTO @ADDRESS_IDS
FROM addresses ad, #addresses vad
WHERE ad.ADDRESS_ID = vad.ADDRESS_ID
INSERT INTO addresses
(LINE1,
  LINE2,
  CITY ,
  STATE_OR_PROVINCE,
  COUNTRY,
OWNER_TYPE,
ADDRESS_TYPE,
OWNER_ID,
IS_ACTIVE,
CREATE_BY,
CREATE_DATE,
CREATE_PROCESS,
UPDATE_BY,
UPDATE_PROCESS )
SELECT LINE1,
  LINE2,
  CITY,
  STATE_OR_PROVINCE,
  COUNTRY,
'CUSTOMER',
'MAIN',
1,
'Y',
2,
GETDATE(),
'test1',
2,
'uptst'
FROM #addresses WHERE ADDRESS_ID NOT IN ( SELECT ID FROM @ADDRESS_IDS)

2) We can also achive same functionality in SQL 2008 using merge query as,


drop table #addresses

create table #addresses (ADDRESS_ID int,
LINE1 varchar(50),
LINE2 varchar(50),
CITY varchar(50),
STATE_OR_PROVINCE varchar(50),
COUNTRY varchar(3)
)

insert into #addresses 
select ADDRESS_ID, isnull(LINE1,'line1merge'), isnull(LINE2,'line2merge'), isnull(CITY,'CITY'), STATE_OR_PROVINCE, COUNTRY from addresses  where ADDRESS_ID in (535,536)

insert into #addresses  values( 538, 'test538', 'test5638', 'te', 't', '38' )

MERGE addresses as ad
USING
(
 SELECT ADDRESS_ID,
   LINE1,
   LINE2,
   CITY,
   STATE_OR_PROVINCE,
   COUNTRY
 FROM #addresses
) as vad
ON ad.ADDRESS_ID = vad.ADDRESS_ID
WHEN MATCHED THEN
 UPDATE SET ad.LINE1 = vad.LINE1,
    ad.LINE2 = vad.LINE2,
    ad.CITY = vad.CITY,
    ad.STATE_OR_PROVINCE = vad.STATE_OR_PROVINCE,
    ad.COUNTRY = vad.COUNTRY
WHEN NOT MATCHED THEN
 INSERT
 (
  ad.LINE1,
  ad.LINE2,
  ad.CITY ,
  ad.STATE_OR_PROVINCE,
  ad.COUNTRY,
  ad.OWNER_TYPE,
  ad.ADDRESS_TYPE,
  ad.OWNER_ID,
  ad.IS_ACTIVE,
  ad.CREATE_BY,
  ad.CREATE_DATE,
  ad.CREATE_PROCESS,
  ad.UPDATE_BY,
  ad.UPDATE_PROCESS
 )
 VALUES
 (
  vad.LINE1,
  vad.LINE2,
  vad.CITY,
  vad.STATE_OR_PROVINCE,
  vad.COUNTRY,
  'CUSTOMER',
  'MAIN',
  1,
  'Y',
  2,
  GETDATE(),
  'test1',
  2,
  'uptst'
 );

We can also create User Defined Table Type which will act like an structure and can be used to passe data as table to Stored Procedure.

Conclusion: Merge query is a very poweful feature provided by SQL Server 2008.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend