March 22, 2018 at 3:00 am
Hi All,
I have a small requirement. so far, we have been using Merge statement to INS and UPD records.
Now , they dont want to go for UPDATE. instead of update they want to perform (deletion of existing record and insert the new record from the staging table).
How can we do that delete and re-insert of the existing records from staging table.
Can anyone help?
demo
=========
drop table [Staging_tbl]
drop table destination_tbl;
CREATE TABLE [dbo].[Staging_tbl]
(
[Id] [int] NULL,
[AddressType] [varchar](20) NOT NULL,
[Address] [varchar](200) NULL,
[City] [varchar](50) NULL,
[CountryName] [varchar](50) NULL,
[EmailAddress] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](30) NULL,
[Mobile1] [varchar](20) NULL,
[Status] [varchar](20) NULL,
[State] [varchar](50) NULL,
[Mobile2] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[Staging_tbl]
SELECT 101,'AddressType 01','Address 01','City 01','CountryName 01','MVK@GMAIL.COM','0409999999','292992992','999999999','ACTIVE','1','99999999'
UNION ALL
SELECT 102,'AddressType 02','Address 02','City 02','CountryName 02','GVK@GMAIL.COM','0402222222','222222222','888888888','ACTIVE','1','88888888'
CREATE TABLE [dbo].[destination_tbl]
(
[Id] [int] NULL,
[AddressType] [varchar](20) NOT NULL,
[Address] [varchar](200) NULL,
[City] [varchar](50) NULL,
[CountryName] [varchar](50) NULL,
[EmailAddress] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](30) NULL,
[Mobile1] [varchar](20) NULL,
[Status] [varchar](20) NULL,
[State] [varchar](50) NULL,
[Mobile2] [varchar](20) NULL
)
GOCREATE TABLE [dbo].[#temp_tbl]
( ActionTaken varchar(40),
[Id] [int] NULL,
[AddressType] [varchar](20) NOT NULL,
[Address] [varchar](200) NULL,
[City] [varchar](50) NULL,
[CountryName] [varchar](50) NULL,
[EmailAddress] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](30) NULL,
[Mobile1] [varchar](20) NULL,
[Status] [varchar](20) NULL,
[State] [varchar](50) NULL,
[Mobile2] [varchar](20) NULL
)
GO
;
MERGE destination_tbl AS t
USING Staging_tbl AS s ON t.EmailAddress = s.EmailAddress
WHEN NOT matched THEN
INSERT VALUES (s.id,s.AddressType,s.[Address],s.City,s.CountryName,s.EmailAddress,s.Phone,s.Fax,s.Mobile1,s.[Status],s.[State],s.Mobile2)
WHEN MATCHED THEN
UPDATE SET
t.id = s.id,
t.[AddressType] = s.[AddressType],
t.[Address]= s.[Address],
t.[City]= s.[City],
t.[CountryName]= s.[CountryName],
t.[EmailAddress]=s.[emailAddress],
t.[Phone] = s.[Phone],
t.[Fax] = s.[Fax],
t.[Mobile1] = s.[Mobile1],
t.[Status] = s.[Status],
t.[State] = s.[State],
t.[Mobile2] = s.[Mobile2]
OUTPUT
$Action Action_Taken,
s.[Id],
s.[AddressType],
s.[Address],
s.[City],
s.[CountryName],
s.[EmailAddress],
s.[Phone],
s.[Fax],
s.[Mobile1],
s.[Status],
s.[State],
s.[Mobile2]
INTO [#temp_tbl];
select * from [Staging_tbl];
select * from [destination_tbl];
select * from [#temp_tbl];
go
INSERT INTO [dbo].[Staging_tbl]
SELECT 103,'AddressType 03','Address 03','City 03','CountryName 03','ABC@GMAIL.COM','04033333333','333333333333','333333333','ACTIVE','1','333333333333'
UNION ALL
SELECT 104,'AddressType 04','Address 04','City 04','CountryName 04','XYZ@GMAIL.COM','04044444444','444444444444','444444444','ACTIVE','1','444444444444'
update [Staging_tbl]
set Address = 'XXXXX' , City=null, CountryName=' '
where id = 101;
;
MERGE destination_tbl AS t
USING Staging_tbl AS s ON t.EmailAddress = s.EmailAddress
WHEN NOT matched THEN
INSERT VALUES (s.id,s.AddressType,s.[Address],s.City,s.CountryName,s.EmailAddress,s.Phone,s.Fax,s.Mobile1,s.[Status],s.[State],s.Mobile2)
WHEN MATCHED THEN
UPDATE SET
t.id = s.id,
t.[AddressType] = s.[AddressType],
t.[Address]= s.[Address],
t.[City]= s.[City],
t.[CountryName]= s.[CountryName],
t.[EmailAddress]=s.[emailAddress],
t.[Phone] = s.[Phone],
t.[Fax] = s.[Fax],
t.[Mobile1] = s.[Mobile1],
t.[Status] = s.[Status],
t.[State] = s.[State],
t.[Mobile2] = s.[Mobile2]
OUTPUT
$Action Action_Taken,
s.[Id],
s.[AddressType],
s.[Address],
s.[City],
s.[CountryName],
s.[EmailAddress],
s.[Phone],
s.[Fax],
s.[Mobile1],
s.[Status],
s.[State],
s.[Mobile2]
INTO [#temp_tbl];
select * from [destination_tbl];
select * from [#temp_tbl];
go
Thanks,
Sam
March 22, 2018 at 11:47 am
That's what SQL Server does behind the scenes
March 23, 2018 at 3:31 pm
Agreed. If instead of updating, you want to delete all rows then re-insert them, you can't make MERGE do it. You have to code a DELETE statement followed by an INSERT statement. I'm not sure why you would ever want to do this instead of a simple UPDATE, could you elaborate on where this requirement comes from?
If you are wanting to move away from MERGE because of slow performance I would advise you do read this first:
In the example code above, the lack of indexes on email address (the join column) is going to kill performance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply