SQL generate unique id when copying multiple records

Question

I have this SP

CREATE PROCEDURE ApplyExistingImageAcrossModel
@capidfrom int,
@capidto int,
@type int
AS
BEGIN

DECLARE @imageid uniqueidentifier

INSERT INTO ImageMetaData
(ImageId,CapId,[Type],Source,DateAdded,OriginalFileName,OriginalUrl,isDefault,Height,Width,ViewType,CapImageId,islive)
SELECT @imageid,@capidto,@type,source,GETDATE(),originalfilename,originalurl,isdefault,height,width,viewtype,capimageid,islive 
FROM ImageMetaData 
WHERE [type] = @type AND capid = @capidfrom

INSERT INTO ImageData
(ImageId,ImageBinary)
SELECT @imageid,ImageBinary 
FROM ImageData A
LEFT JOIN ImageMetaData B
ON A.ImageId = B.ImageId
WHERE B.[type] = @type 
AND B.capid = @capidfrom

END

The problem is that the select can return multiple records for a particular capid, and I want to copy them all, but each record needs a unique @imageid which i'm only generating once, I need to use that imageid in both tables.

Thanks

I'm not sure if it's a duplicate or not - If it is please let me know:https://stackoverflow.com/a/34832231/3094533 — Zohar Peled, Oct 18 '17 at 14:58
I would suggest using better aliases than A, B. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 Have you considered using the OUTPUT clause from your initial insert? https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql — Sean Lange, Oct 18 '17 at 15:24

score 0 · Answer 1 · answered Oct 19 '17 at 16:43

Instead creating one variable, declare table variable to store the unique IDs. Then, use OUPUT clause to record all the IDs. Something like this:

DECLARE @DataSource TABLE 
(
    @imageid uniqueidentifier
);

INSERT INTO ImageMetaData(ImageId,CapId,[Type],Source,DateAdded,OriginalFileName,OriginalUrl,isDefault,Height,Width,ViewType,CapImageId,islive)
OUTPUT inserted.ImageId
INTO @DataSource (@imageid)
SELECT NEWID(),@capidto,@type,source,GETDATE(),originalfilename,originalurl,isdefault,height,width,viewtype,capimageid,islive 
FROM ImageMetaData 
WHERE [type] = @type AND capid = @capidfrom

Now, in the @DataSource table we have the inserted imageIDs. Do with them whatever you want.

SQL generate unique id when copying multiple records

1 Answers1