How to use the SQL Server OUTPUT clause to upgrade

Question

DECLARE @t2 AS TABLE(id INT) INSERT INTO dbo.EntityMaster (EntityType) OUTPUT INSERTED.EntityId INTO @t2 SELECT 'G' FROM #tmp 

#tmp is a temporary table containing data loaded from xml. I need to create an EntityId for every entry contained in #tmp . This can be done by inserting the record first into the EntityMaster table, and then insert this entity back into #tmp for each record.

Instead of inserting an entry into @t2 , I need to update #tmp for each entry.

Any opportunity?

Answer 1

Try something similar, you still have to use a temporary table, but reading it is not so bad, and it does its job.

  CREATE TABLE #tmp
 (
     tmpID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
     xmlData VARCHAR (255),
     EntityId INT
 )
 DECLARE @ t2 TABLE
 (
     tmpID INT,
     EntityId INT
 )

 MERGE dbo.EntityMaster AS EM
 USING
 (
     SELECT tmpID,
         xmlData,
         EntityId
     FROM #tmp
 ) AS X
     ON EM.EntityId = X.EntityId
 WHEN NOT MATCHED THEN
     INSERT (EntityType)
     VALUES (X.xmlData)
 OUTPUT X.tmpID, INSERTED.EntityId
 INTO @ t2 (tmpID, EntityId);

 UPDATE T
 SET EntityId = T2.EntityId
 FROM @ t2 T2
 INNER JOIN #tmp T
     ON T2.tmpID = T.tmpID

Answer 2

This would be easier to do in SQL, which inserts the XML records into the #tmp table.

Consider the following @recs table, which can be thought of as a set of records generated from XML:

 declare @recs table (val varchar(255)) insert into @recs values ('this'), ('is'), ('a'), ('test') 

You can easily add an incrementing integer to each record as follows:

 select row_number() over (order by (select 1)) as id, val from @recs 

The result is as follows:

 id val 1 this 2 is 3 a 4 test 

Could you use row_number() over (order by (select1)) to generate the identifiers you need at the same time that the records are inserted in #tmp ?