SQL: Is it possible to insert into two tables at the same time with autogenerated id output from first table into second

Question

My database contains two tables FileStore and FileRepository.

The FileStore table contains 3 columns Id (autogenerated uniqueidentifier), FileName and Description -> In Initial state with demo data

The FileRepository table contains 3 columns Id (autogenerated uniqueidentifier), Name and FileId (this refers to the Id column in FileStore table) -> In Initial state with demo data

I get an array of FileStore Ids as a search criteria. With the same I need to create a duplicate row in the FileStore table first for each of the criteria satisfied. I need to do the duplicate row creation for the second table FileRepository as well based on the same data on the FileId column. Here but I need to update the newly created row's FileId column with the autogenerated Id column from the FileStore operation.

Say referring to the attached images if I need to duplicate Files File 1(with Id b3304dc4-4f2e-46d4-8f64-a597edb02e96) and File 2(with Id 7cb40baf-1ecf-4e5f-92ff-57029a20e623) this is how the tables should have data after the operation FileStore db after duplication should have data thus:

FileRepository db after duplication should have data thus:

Which is the best way to do this? Is it possible to achieve this with a query without any loops?

For the first table I can insert and get the inserted Ids thus:

INSERT INTO FileStore(FileName,Description)
OUTPUT INSERTED.Id as InsertedIds
SELECT
FileName, Description
from FileStore
where Id IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623')

But was unsure about the query to update the FileRepository table which takes the InsertedIds as input

INSERT INTO FileRepository(Name,FileId)
SELECT
Name, {{How do I use InsertedDetails here?}}
from FileRepository
where FileId IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623')

PS: This is just a reference table I created for this question. Please let me know in case I need to make it more clear

Answer 1

As I mention, you need to use the OUTPUT clause. This is pseudo-SQL in the abscence of consumable sample data and expected results. You'll need to remove/change the parts in braces ({}) appropriately:

DECLARE @Output table (ID uniqueindentifier,
                       {Other Columns});

INSERT INTO FileStore(FileName,Description)
OUTPUT INSERTED.Id, {Other Columns}
INTO @Output
SELECT FileName,
       Description
from FileStore
where Id IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623');

INSERT INTO FileRepository(Name,FileId)
SELECT FR.Name,
       O.ID
from FileRepository FR
     {CROSS} JOIN @Output O {ON Some Condition}
where FileId IN ('b3304dc4-4f2e-46d4-8f64-a597edb02e96','7cb40baf-1ecf-4e5f-92ff-57029a20e623')