Reasons to move to SQL 2008 - MERGE

One of the great things for me currently is that my client is x64 SQL 2008, which means I can use all those features that make SQL Server 2008 such a great product.

Anyway I have a very poor example of resolving a batch insert problem – you should realise that there are a number of restrictions and rules concerning MERGE but I’ll just pass these by for this example.

I have a number of records passed into a stored procedure as a table ( another great feature of sql 2008 ), these first have to check for existence in a sub table ( we’ll call it ) if one of the passed values exists in this table we must ignore the row, otherwise we have to insert the row and return the key for the inserted value to pass into a second table with the remainder of the data.

So what we’re doing doing is an insert into two tables based around an if exists for the first table. For a single row it’s not difficult to code but ifn you want to handle say 50 rows then we’re into temporary tables, loops – well it gets messy ( I’d never use triggers just in case someone thinks of using those!! )

Here’s my very poor working example, used with a proc you can create a table type and pass the table into the proc; but you can’t do this in  a straight query – well I got some very strange errors, but maybe it was the example I wrote!

( not quite sorted my publisher so lost the formatting – fixed next time )

--
-- create base tables and data
--
IF OBJECT_ID('dbo.Widgets', 'U') IS NOT NULL DROP TABLE dbo.Widgets;
create Table dbo.Widgets
( WidgetID int identity(1,1) not null,
WidgetName varchar(50) not null,
CreateDate datetime not null
);
Alter table dbo.Widgets add constraint PK_Widgets Primary Key Clustered (WidgetID);
alter table dbo.Widgets add constraint UK_Widgets_Name unique (WidgetName);
--
insert into dbo.Widgets(WidgetName,CreateDate)
values
('BlueMeerkat1',GETDATE()-1),
('BlueMeerkat2',GETDATE()-2),
('BlueMeerkat3',GETDATE()-3),
('BlueMeerkat4',GETDATE()-4),
('BlueMeerkat5',GETDATE()-2),
('BlueMeerkat6',GETDATE()-4),
('BlueMeerkat7',GETDATE()-3),
('BlueMeerkat8',GETDATE()-2);
--
IF OBJECT_ID('dbo.Wotsits', 'U') IS NOT NULL DROP TABLE dbo.Wotsits;
create table dbo.Wotsits
( WotsitID int identity(1,1) not null,
  WotsitName varchar(50) not null,
  WidgetID int not null,
  CreateDate datetime not null
);
Alter table dbo.Wotsits add constraint PK_Wotsits Primary Key Clustered (WotsitID);
alter table dbo.Wotsits add constraint UK_Wotsits_Name unique (WotsitName);
--
insert into dbo.Wotsits(WotsitName,WidgetID,CreateDate)
values
('YellowWotsit1',1,GETDATE()-1),
('YellowWotsit2',2,GETDATE()-2),
('OrangeWotsit1',3,GETDATE()-3),
('OrangeWotsit2',4,GETDATE()-4),
('CheeseWotsit1',5,GETDATE()-2),
('CheeseWotsit2',6,GETDATE()-4),
('OnionWotsit1',7,GETDATE()-3),
('OnionWotsit2',8,GETDATE()-2);
--
--
DECLARE @Table table( SeqKey INT identity(1,1) NOT NULL PRIMARY KEY,
  WidgetName varchar(50) not null,
  WotsitName varchar(50) not null);
--
INSERT INTO @Table(WidgetName,WotsitName)
VALUES
('BlueMeerkat3','LeekWotsit1'),
('BlueMeerkat9','LeekWotsit2'),
('BlueMeerkat3','LeekWotsit3'),
('BlueMeerkat4','LeekWotsit4'),
('BlueMeerkat10','LeekWotsit5'),
('PinkMeerkat6','LeekWotsit6'),
('PinkMeerkat4','LeekWotsit7'),
('PinkMeerkat5','LeekWotsit8');
--
-- simulate the proc here
--
declare @results table (WidgetID int not null,WidgetName varchar(50) not null);
--
MERGE INTO dbo.Widgets AS wi 
      USING @Table AS tbl 
            ON wi.WidgetName = tbl.WidgetName
WHEN NOT MATCHED THEN -- if not there insert
      INSERT ( WidgetName,CreateDate)
            VALUES ( tbl.WidgetName,getdate())
OUTPUT  -- return the value and id of the rows we did insert
            inserted.WidgetID,inserted.WidgetName into @results(WidgetID,WidgetName);
--
MERGE INTO dbo.Wotsits AS wo
USING @Table AS tbl join @results as RES on tbl.WidgetName = RES.WidgetName
  ON wo.WotsitName = tbl.WotsitName
WHEN NOT MATCHED THEN -- if not there insert the row
  INSERT (WotsitName, WidgetID, CreateDate)
  VALUES ( tbl.WotsitName, RES.WidgetID, getdate() )
OUTPUT -- output what we inserted in plain text
$action, inserted.WotsitID,inserted.WotsitName;
-- endproc

Technorati Tags:


Published 11 March 2009 23:08 by GrumpyOldDBA

Comments

No Comments