Reasons to move to SQL 2008 MERGE pt 2
In my previous post with the MERGE statement it was assumed that the pairs of data would be unique and the second set would not already exist. I can’t say that I’d actually put together a really good example and I’m not saying that this is the most brilliant of examples but it possibly makes more sense than the last!
The brief description here is that here we’re adding unique people to our database and adding a lookup to their occupation. What we want to do is pass a batch of people and their job title to a procedure, we’d like the personal details to pick up the foreign key for the Job Title, but we’d also like to add any job titles we don’t already have – all at the same time!
As I think I’d mentioned previously the MERGE statement cannot deal with duplicate entries and will throw an error which ends the batch. This piece of code gets around this and will cope with duplicate Job Titles, Existing Job Titles and New Job Titles.
I’m assuming you’d not want duplicate PersonNames.
- The code below will create and populate two tables with some data, JobTitle and People, as before the columns that we’re using to merge on must be unique.
- Once the tables have been populated we fill a table variable with some values and then merge it into the JobTitles first picking up the foreign key and then into the Person Table.
- @max is set to the maximum number of duplicates and then we loop though the merge using distinct values adding any new job titles.
- The output from the MERGE is passed into another table variable which is then joined with the original table to be merged into the Person table. ( To capture the ID of the already existing JobTitles I’m just updating the created date )
I’ve left the output as selects so you can see clearly what is happening.
My personal view is that SQL 2008 BOL is pretty rubbish, I wasn’t too pleased with 2005 BOL and I think a lot of the careful work in SQL 2000 BOL has been lost – try looking up Federated Servers – just like Vista too many clicks leading to other clicks which don’t actually lead you anywhere! At least the MERGE statement is in one section, but I don’t find the explanations or the syntax very easy to follow.
Very powerful stuff, like the pivot command – but a nightmare to follow!
--
-- create base tables and data
--
IF OBJECT_ID('dbo.JobTitle', 'U') IS NOT NULL DROP TABLE dbo.JobTitle;
create Table dbo.JobTitle
( JobID int identity(1,1) not null,
JobName varchar(50) not null,
CreateDate datetime not null
);
Alter table dbo.JobTitle add constraint PK_JobTitle Primary Key Clustered (JobID);
alter table dbo.JobTitle add constraint UK_JobTitle_Name unique (JobName);
--
insert into dbo.JobTitle(JobName,CreateDate)
values
('Welder',GETDATE()-1),
('Fabricator',GETDATE()-2),
('Roof Carpenter',GETDATE()-3),
('Roof Tiler',GETDATE()-4),
('Plumber',GETDATE()-2),
('Electrician',GETDATE()-4),
('Dog Walker',GETDATE()-3),
('Mouse Handler',GETDATE()-2);
--
IF OBJECT_ID('dbo.People', 'U') IS NOT NULL DROP TABLE dbo.People;
create table dbo.People
( PersonID int identity(1,1) not null,
PersonName varchar(50) not null,
JobID int not null,
CreateDate datetime not null
);
Alter table dbo.People add constraint PK_People Primary Key Clustered (PersonID);
alter table dbo.People add constraint UK_People_Name unique (PersonName);
--
insert into dbo.People(PersonName,JobID,CreateDate)
values
('John Smith',1,GETDATE()-1),
('Andy Williams',2,GETDATE()-2),
('Buddy Holly',3,GETDATE()-3),
('Ozzie Osbourne',4,GETDATE()-4),
('Sharleen Spiteri',5,GETDATE()-2),
('Christoph Doom Schneider',6,GETDATE()-4),
('Paul Landers',7,GETDATE()-3),
('Oliver Riedel',8,GETDATE()-2);
--
--
DECLARE @Table table( SeqKey INT identity(1,1) NOT NULL PRIMARY KEY,
JobName varchar(50) not null,
PersonName varchar(50) not null);
declare @max tinyint;
--
INSERT INTO @Table(JobName,PersonName)
VALUES
('Welder','Jonas Renkse'),
('Welder 2nd Class','Anders Nystrom'),
('Grave Digger','Fred Norrman'),
('Assistant Chef','Mattias Norrman'),
('Welder','Daniel Liljekvist'),
('Mouse Walker','Anne-Marie Helder'),
('Mouse Handler','Bryan Josh'),
('Welder','Joe Satriani'),
('Drummer','Nick Mason'),
('Drummer','Heather Findlay');
--
-- simulate the proc here
--
select @max = max(maxdups) from
( select distinct JobName, COUNT(*) as maxdups from @Table group by JobName having COUNT(*) >1) as z;
declare @results table (JobID int not null,JobName varchar(50) not null);
while @max>=1
begin
MERGE INTO dbo.JobTitle AS wi
USING ( select distinct Jobname, COUNT(*) as HowMany from @Table group by JobName having COUNT(*) = @max) AS tbl
ON wi.JobName = tbl.JobName
WHEN NOT MATCHED THEN -- if not there insert
INSERT ( JobName,CreateDate)
VALUES ( tbl.JobName,getdate())
--OUTPUT inserted.JobID,inserted.JobName into @results(JobID,JobName);
when matched then
UPDATE SET CreateDate = GETDATE()
OUTPUT inserted.JobID,inserted.JobName into @results(JobID,JobName);
set @max-=1;
end
--
select * from @Table AS tbl join @results as RES on tbl.JobName = RES.JobName
--
MERGE INTO dbo.People AS wi
USING ( select tbl.PersonName,res.JobID from @Table AS tbl join @results as RES on tbl.JobName = RES.JobName ) as peop
on wi.PersonName = peop.PersonName
WHEN NOT MATCHED THEN -- if not there insert
INSERT ( PersonName,JobID,CreateDate)
VALUES ( peop.PersonName,peop.JobID,getdate())
OUTPUT $action,inserted.*;
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }