Way to do MERGE with update source when target not match?

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching its values.

Now, from what I am seeing, the MERGE function doesn't appear to support what I am trying to do, unless I am doing it wrong (completely probable, I'm not a SQL guy -- .NET). Anyways, I really just learned about the MERGE this evening, and it seemed like what I should use at first, but it doesn't seem to have the functionality I need, since I can't just get the ID that existed from before, which I need for the rest of a stored procedure. My main focus was to see if I could get this to work, but the other issue is I need to be sure that the code handles potential concurrent calls to a stored procedure that would run through the MERGE. I know I don't have locking in there now, but I was just trying to get it to work at this point. So, what I have....

Setup

```
DECLARE @Metadata TABLE
(
MetadataId INT PRIMARY KEY IDENTITY(1,1),
MetadataTypeId INT,
MetadataTypeValueId INT
)

INSERT INTO @Metadata
VALUES
(1,23),(2,32),(2,33),(2,43),(1,24),(3,33),(1,20)

--Original Table Data
SELECT * FROM @Metadata

DECLARE @MergeMetadata TABLE
(
MetadataId INT,
MetadataTypeId INT,
MetadataTypeValueId INT
)

INSERT INTO @MergeMetadata (MetadataTypeId, MetadataTypeValueId)
VALUES
(2,32),(2,35),(3,34),(4,1),(1,23)

--Metadata that needs added and/or ID'd if exists
SELECT * FROM @MergeMetadata

DECLARE @FinalMetadata TABLE
(
MetadataId INT,
MetadataTypeId INT,
MetadataTypeValueId INT
)

INSERT INTO @FinalMetadata (MetadataId, MetadataTypeId, MetadataTypeValueId)
VALUES
(2,2,32),(8,2,35),(9,3,34),(10,4,1),(1,1,23)

--This is the same Type/TypeValue Id's from the table above
--However, they've been ID'd with existing IDs from the original table
--Or inserted and ID'd if they didn't exist
--Order doesn't matter for the result, I just need the IDs

Updates are tracked by MERGE by providing:

• the $Action value UPDATE;

• the old values in the memory table deleted; and

• the new values in the memory table inserted.

Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.

StackExchange Database Administrators Q#71807, answer score: 3