-1

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

FileStore Initial state

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

FileRepository Initial state

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: Updated FileStore db

FileRepository db after duplication should have data thus: Updated FileRepository db

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

Suraj Nair
  • 561
  • 1
  • 8
  • 27
  • 1
    A DML statement can only affect one object at a time. If you need to effect multiple objects, you need multiple statements. Seems like you just need to be using the `OUTPUT` clause here, to get the inserted data, and then `INSERT` that into another table? To answer your question *"Is it possible to achieve this with a query without any loops"* There are ***very*** few times you ever need a loop in SQL. SQL isn't a programming language, it's a query language; it excels at set-based solutions are performs poorly at iterative ones. If you're thinking of looping, you're probably doing it wrong. – Thom A May 06 '20 at 08:46
  • Well even I was of the same assumption of using the OUTPUT clause but lil unsure of using it when we have multiple return values – Suraj Nair May 06 '20 at 08:54
  • `OUTPUT` returns a dataset, so I'm not sure what your mean by when you have multiple values. `OUTPUT` can only ever return 1 dataset, not many. – Thom A May 06 '20 at 08:57
  • Well probably I was not clear with the doubt I had. I agree the OUTPUT clause returns a dataset. Well I needed to input the returned Ids into the second table mapping based on which file duplicated returns the corresponding Id. The mapping table actually needs to be updated accordingly – Suraj Nair May 06 '20 at 09:26
  • @Larnu Have updated my question to make it more clear – Suraj Nair May 06 '20 at 10:40

2 Answers2

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')
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • If you feel this answer is unhelpful, I would greatly appreciate you explaining why so that I can look to improve it. Thank you. – Thom A May 08 '20 at 11:15
-2
  1. At first, insert your parent table's data.

  2. Then

    INSERT INTO FileRepository (`id`, `Name`,`FileId`)   
VALUES('id_or_auto_generated','name value', SELECT SCOPE_IDENTITY())

SELECT SCOPE_IDENTITY(); will return the most recently auto-generated id of the SQL server database connection.

For other databases : Follow this post

Mahfuz Ahmed
  • 721
  • 9
  • 23