Recover Merge SQL Statement in Microsoft SQL Server 2008 Product Overview
Previously, SQL Statement for "insert data if not found, update record if data already exists" :
If (select 1 from table where id=@id)
Update
Else
Insert
With merge function, we can do this way
MERGE Table1 cs
USING Table2 cd
on Table1.id=cd.table1_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
Merge function can do more
If not match by target
If not match by source
Output
Reference
SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005
SQL Server: Best way to Update row if exists, Insert if not
The MERGE Statement in SQL Server 2008
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Using SQL Server 2008's MERGE statement
---------------------------------------------------------------------------------
Example
Create database temptest1456
use temptest1456
CREATE TABLE [dbo].[Customers](
[Cust_ID] [int] NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_Surname] [varchar](50) NOT NULL,
[Cust_Email] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TYPE [dbo].[customertype] AS TABLE(
[Cust_ID] [int] NULL,
[Cust_Name] [varchar](50) NULL,
[Cust_Surname] [varchar](50) NULL,
[Cust_Email] [varchar](50) NULL
)
GO
insert into Customers values (1,'1steven','1gerrard','1sg@liverpool.com')
insert into Customers values (2,'1jamie','1gerrard','1sg@liverpool.com')
insert into Customers values (3,'1Kamie','1Kerrard','1sg@liverpool.com')
CREATE procedure newcustomer(@cust_id varchar(50),@cust_name varchar(50),@cust_surname varchar(50),@cust_email varchar(50))
as
begin
IF exists (select 1 FROM Customers where Cust_ID=@cust_id)
update Customers
set Cust_Name=@cust_name,
Cust_Surname=@cust_surname,
Cust_Email=@cust_email
where Customers.Cust_ID= @cust_id
ELSE
insert into customers
(Cust_ID,Cust_Name,Cust_Surname,Cust_Email)
values (@cust_id,@cust_name,@cust_surname,@cust_email)
END
Exec newcustomer 3, '2jamie','2caragher','2jc@liverpool.com'
Exec newcustomer 4, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 6, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 7, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 8, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 9, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 10, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 11, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 12, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 13, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 14, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 16, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 17, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 18, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 19, '2lamie','2earagher','2lc@liverpool.com'
Alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (5, '3jamie','3caragher','3jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (6, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (10, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (11, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (12, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (13, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (14, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (18, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (19, '3lamie','3earagher','3lc@liverpool.com')
exec newcustomer1 @customer_details
ALTER procedure newcustomer (@Customer_details customertype READONLY)
as
BEGIN
MERGE Customers cs
USING @Customer_details cd
on cs.cust_id=cd.cust_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
END
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (22, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (23, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (24, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (25, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (26, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (27, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (28, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (29, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (30, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (31, '4lamie','4earagher','4lc@liverpool.com')
exec newcustomer1 @customer_details
drop database temptest1456
No comments:
Post a Comment