插入多行，获取ids，并对此ids执行操作

Question

我有一个问题，这是否可以在SQL中完成？

我有两张桌子:表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     |
+----+---------+------------+-----------+

下面是Orders表(在OrderId下的表1中引用了它的id )

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

现在，对于表1中的OrderId类I的每个重复组合，我想

• 使用新Id在订单中创建订单条目的副本
• 获取给定副本的条目，其中OrderType=Type2来自Table1
• 用Orders表中新创建的条目更新其id

因此，这些表最终将按以下方式更新：

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     |
+----+---------+------------+-----------+

命令：

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

因此，我知道如何在Orders表中创建行的副本并获取其id：

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

SELECT SCOPE_IDENTITY()

我知道如何在表1中找到重复的in：

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

我不知道的是，如何为所有的副本运行这个。我的意思是创建某种foreach循环，在这个foreach循环中，在Orders表中插入一个新行，获取它的id，并用这个id更新表1的OrderId值。最让我困惑的是，如果这样做能够执行这样的多行插入，并且每次插入时仍然能够检索it的话。

我在想，对于单个查询来说，这是不是太过分了，或者我是以一种错误的方式来处理这个问题(也许可以更多地按顺序执行呢？)

谢谢!

Answer 1

以下是基于反馈的更新版本。

最初的版本只是将OrderIds增加了1，但是它们在一个标识字段中并且是自动创建的。小提琴中的原始代码/等

由于订单的标识字段，逻辑现在如下所示

• 识别上面的任何副本
• 为上面的命令创建适当数量的订单，并记录订单and
• 更新表T1中的相关OrderID(其中原始行具有OrderType = 'Type 2')
• 使用相关的SomeProp1和SomeProp2更新Orders表

注意，可以删除一个步骤(而不是插入新订单，然后更新它们)，但我想非常小心，这样您就可以将新订单与相关的旧订单相匹配。

事务就在那里，以帮助隔离更改；但是，如果同时运行此过程几次，则确实需要小心。

下面是一个包含以下代码的小提琴。

/*    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