you need at least a table variable in between
like below
declare @TableA table
(
ID varchar(10),
FirstName varchar(50),
[Status] int
)
insert @TableA
values('01', 'Person A', 1),
('02', 'Person B', 1),
('03', 'Person C', 0),
('04', 'Person D', 0)
declare @TableB table
(
ID varchar(10),
FirstName varchar(50)
)
insert @TableB
values('01', 'Person A'),
('02', 'Person B')
declare @Inserted_IDs table
(
ID varchar(10)
)
insert @TableB
OUTPUT INSERTED.ID INTO @Inserted_IDs
select ID,Firstname
FROM @TableA a
WHERE NOT EXISTS ( SELECT 1 FROM @TableB WHERE FirstName = a.FirstName)
UPDATE a
SET Status=1
FROM @TableA a
JOIN @Inserted_IDs i
ON i.ID = a.ID
SELECT * FROM @TableA
SELECT * FROM @TableB
output
---------------------------------------------------
ID FirstName Status
-------------------------------------
01 Person A 1
02 Person B 1
03 Person C 1
04 Person D 1
ID FirstName
-----------------------
01 Person A
02 Person B
03 Person C
04 Person D
Also not sure why you're having ID field as varchar.
Its better to make it integer type otherwise you may have difficulty in manipulations using it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Update selected Records and insert into Table B
Posted - 03/12/2013 : 00:36:43
