Insert multiple rows, get ids, and perform action on this ids

I have a question if this is possible to do in SQL

I have two tables: Table 1:

+----+---------+------------+-----------+
| Id | OrderId | CategoryId | OrderType |
+----+---------+------------+-----------+
|  1 |      10 |         15 | Type1     |
|  2 |      10 |         15 | Type2     |
|  3 |       9 |         17 | Type1     |
|  4 |      99 |         17 | Type2     |
|  5 |      20 |         25 | Type1     |
|  6 |      20 |         25 | Type2     |
+----+---------+------------+-----------+

And here's the Orders table (its id is referenced in table 1 under OrderId)

+----+-----------+-----------+
| Id | SomeProp1 | SomeProp2 |
+----+-----------+-----------+
|  9 | test1     | test2     |
| 99 | test1     | test2     |
| 10 | test3     | test4     |
| 20 | test5     | test6     |
+----+-----------+-----------+

Now for each duplicate combination of OrderId-CategoryId in Table 1, I want to

create a copy of the Order entry in Orders with a new Id
get the entry of given duplicate, where OrderType=Type2 from Table1
update its id with newly created entry in Orders table

So eventually the tables are updated as follows:

Table1:

+----+---------+------------+-----------+
| Id | OrderId | CategoryId | OrderType |
+----+---------+------------+-----------+
|  1 |      10 |         15 | Type1     |
|  2 |      11 |         15 | Type2     |
|  3 |       9 |         17 | Type1     |
|  4 |      99 |         17 | Type2     |
|  5 |      20 |         25 | Type1     |
|  6 |      21 |         25 | Type2     |
+----+---------+------------+-----------+

Orders:

+----+-----------+-----------+
| Id | SomeProp1 | SomeProp2 |
+----+-----------+-----------+
|  9 | test1     | test2     |
| 99 | test1     | test2     |
| 10 | test3     | test4     |
| 11 | test3     | test4     |
| 20 | test5     | test6     |
| 21 | test5     | test6     |

So I know how to create a copy of a row in Orders table and get its id:

insert into Orders
(SomeProp1, SomeProp2)
SELECT 
SomeProp1, SomeProp2
from Orders

SELECT SCOPE_IDENTITY()

I know how to find duplicate ids in Table 1:

select OrderId from Table1
    GROUP BY OrderId, CategoryId
    HAVING COUNT(OrderId) > 1

The thing that I don't know, is how to run this for all of the duplicates found. I mean create some kind of a foreach loop, and in this foreach loop insert a new row into Orders table, get its id, and update Table1's OrderId value with this id. The thing, that puzzles me the most is, if this is possible to perform such inserts of multiple rows, and still being able to retrieve ids each time it gets inserted.

I'm wondering if this isn't too much for a single query, or I'm approaching this in a wrong way (maybe it's possible to do it more sequentially?)

Thanks!

Solution

Here's an updated version based on feedback below.

The original version simply incremented OrderIds by 1, but they're in an IDENTITY field and auto-created. Original code/etc in DB_fiddle

Because of the identity field for orders, the logic is now as follows

Identify any duplicates as above
Create an appropriate number of orders for the above, and record the OrderIDs
Update the relevant OrderIDs in table T1 (where the original rows had OrderType = 'Type 2')
Update the Orders table with the relevant SomeProp1 and SomeProp2

Note it may be possible to remove one step (instead of inserting new orders, then updating them) but I'd want to be really careful that you could match the new orders to the relevant old orders.

The transaction is in there to help with isolating the changes; but you do need to be careful if running this process several times concurrently.

Here's a DB_Fiddle with the code below.

/*    DATA SETUP    */

CREATE TABLE #T1 (Id int, OrderID int, CategoryId int, OrderType nvarchar(50))
INSERT INTO #T1 (Id, OrderID, CategoryId, OrderType) VALUES
(1, 10, 15, N'Type1'), 
(2, 10, 15, N'Type2'), 
(3,  9, 17, N'Type1'), 
(4, 99, 17, N'Type2'), 
(5, 20, 25, N'Type1'), 
(6, 20, 25, N'Type2')

CREATE TABLE #Orders (Id int NOT NULL IDENTITY(1,1), SomeProp1 nvarchar(50), SomeProp2 nvarchar(50))

SET IDENTITY_INSERT #Orders ON;
INSERT INTO #Orders (Id, SomeProp1, SomeProp2) VALUES
( 9, N'test1', N'test2'),
(99, N'test1', N'test2'),
(10, N'test3', N'test4'), 
(20, N'test5', N'test6')
SET IDENTITY_INSERT #Orders OFF;


/*    WORKING TABLES    */

CREATE TABLE #OrderChanges (OCId_temp int IDENTITY(1,1), OrderId_new int)

CREATE TABLE #Dupes (DupesId_temp int IDENTITY(1,1), OrderID int, CategoryId int)


/*    PROCESSING    */

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO #Dupes (OrderID, CategoryID)
        SELECT  OrderID, CategoryID 
        FROM    #T1 
        GROUP BY OrderID, CategoryID 
        HAVING  COUNT(*) > 1

    IF EXISTS(SELECT * FROM #Dupes)
        BEGIN

        -- Create appropriate number of new orders, to get IDs (blank for the moment)
        INSERT INTO #Orders (SomeProp1, SomeProp2)
            OUTPUT  inserted.Id
            INTO    #OrderChanges (OrderID_new)
            SELECT  NULL, NULL
            FROM    #Dupes

        -- Should now have same number of rows, with matching IDENTITY Ids, in #Dupes and #OrderChanges

        -- Update #T1
        UPDATE      T1
            SET     OrderId = OC.OrderID_new
            FROM    #T1 AS T1
                    INNER JOIN #Dupes AS Dupes ON T1.OrderID = Dupes.OrderID AND T1.CategoryId = Dupes.CategoryId
                    INNER JOIN #OrderChanges AS OC ON Dupes.DupesId_temp = OC.OCId_temp
            WHERE   T1.OrderType = N'Type2'

        -- Update Orders
        UPDATE      Orders
            SET     SomeProp1 = PrevOrders.SomeProp1,
                    SomeProp2 = PrevOrders.SomeProp2
            FROM    #Orders AS Orders
                    INNER JOIN #OrderChanges AS OC ON Orders.Id = OC.OrderId_new
                    INNER JOIN #Dupes AS Dupes ON OC.OCId_temp = Dupes.DupesId_temp
                    INNER JOIN #Orders AS PrevOrders ON Dupes.OrderID = PrevOrders.Id
    
        END
    
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH


/*    REPORTING AND WRAPUP     */

SELECT * FROM #T1 ORDER BY Id
SELECT * FROM #Orders ORDER BY Id

DROP TABLE #OrderChanges
DROP TABLE #Orders
DROP TABLE #T1
DROP TABLE #Dupes