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 that contains data loaded from an xml. I need to generate EntityId for each record contained in #tmp. It can be done by inserting record first into EntityMaster table then insert this entityid back into #tmp for each record.
Instead of inserting record into @t2, I need to update #tmp for each record.
Any possibility?
Try Something like this, you still have to use the temp table but it's not too bad to read and it gets the job done.
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
This would be easier to accomplish in the SQL that inserts the XML records into the #tmp table.
Consider the following @recs table, which could 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 like this:
select row_number() over (order by (select 1)) as id, val from @recs
The result looks like this:
id val
1 this
2 is
3 a
4 test
Could you use row_number() over (order by (select1)) to generate the ids you need at the same time the records are being inserted into #tmp?
