Grumpy Old DBA - Beware the darkside !

--
-- 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