.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 >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

How to use Merge query in SQL 2008

Posted By: Mahesh     Posted Date: June 21, 2012    Points:5   Category :Sql Server
How to use Merge query in SQL 2008

Responses
Author: Mahesh             
Posted Date: June 21, 2012     Points: 20   

Merge query in SQL 2005
---------------------------------
drop table #addresses

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

insert into #addresses
select ADDRESS_ID, isnull(LINE1,'line1merge'), isnull(LINE2,'line2merge'), isnull(CITY,'CITY'), STATE, 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 = vad.STATE,
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,
COUNTRY,
OWNER_TYPE,
ADDRESS_TYPE,
OWNER_ID,
IS_ACTIVE,
CREATE_BY,
CREATE_DATE,
CREATE_PROCESS,
UPDATE_BY,
UPDATE_PROCESS )
SELECT LINE1,
LINE2,
CITY,
STATE,
COUNTRY,
'CUSTOMER',
'MAIN',
1,
'Y',
2,
GETDATE(),
'test1',
2,
'uptst'
FROM #addresses WHERE ADDRESS_ID NOT IN ( SELECT ID FROM @ADDRESS_IDS)


select top 5 * from addresses order by update_date desc --where ADDRESS_ID in (536,537)
select * from #addresses
----------------------------

Merge Query in SQL 2008
-------------------------------
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'
);

--select * from addresses
--select * from #addresses
------------------------------------------

Author: Syed Shakeer Hussain             
Posted Date: July 07, 2012     Points: 20   

Hi,

what is the problem you are facing.



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

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