How to insert master detail records in T-SQL?

Question

I need to copy some master-detail records, along the lines of:

INSERT INTO Order
(
    SupplierId
   ,DateOrdered
)
SELECT 
   SupplierID
  ,DateOrdered
FROM Order
WHERE SupplierId = 10

DECLARE @OrderId int;

Select @OrderId = Scope_Identity;

INSERT INTO OrderItem
(
   Quantity
  ,ProductCode
  ,Price
  ,FkOrderId
)
SELECT 
  Quantity
 ,ProductCode
 ,Price 
 ,FkOrderId
FROM OrderItem 
WHERE FkOrderId = @OrderId

This will not work. The reason is that there are multiple Orders for Supplier = 10. So what is the best way to iterate through each Order where Supplier = 10, Add the order, and then add the relevant child OrderItem BEFORE going onto the next Order Record where supplier=10. I think I am talking about batching, possibly cursors, but I am a newbie to T-SQL / Store Procedures.

I would appreciate advice on the above.

Thanks.

EDIT

Some more information which I hope will clarify by virtue of some sample data.

Original Order Table

Id       SupplierId         DateOrdered
 1          10                01/01/2000
 2          10                01/01/2000

Original OrderItem Table

Id       Quantity      ProductCode     Price      FkOrderId
 1          20             X1           100           1
 2          10             Y1            50           1
 3          30             X1           100           2
 4          20             Y1            50           2

Final Order Table

Id       SupplierId         DateOrdered
 1          10                01/01/2000
 2          10                01/01/2000
 3          10                01/01/2000    (Clone of 1)
 4          10                01/01/2000    (Clone of 2)

Final OrderItem Table

Id       Quantity      ProductCode     Price      FkOrderId
 1          20             X1           100           1
 2          10             Y1            50           1
 3          30             X1           100           2
 4          20             Y1            50           2
 5          20             X1           100           3 (Clone of 1, linked to clone Order=3)
 6          10             Y1            50           3 (Clone of 2, linked to clone Order=3)
 7          30             X1           100           4 (Clone of 3, linked to clone Order=4)
 8          20             Y1            50           5 (Clone of 4, linked to clone Order=4)

So I need some help with the code can do this cloning of Order and OrderItem to achieve the "final" table records.

It seems I need to do something like:

For each matching record in "Order"

  Clone Order Record
  Clone OrderItem Record where FkOrderId = OldOrderId

Next

Answer 1

This answers your question (no cursors either)

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE [Order]
(
    Id Int Primary Key Identity,
    SupplierId Int,
    DateOrdered Date
)

SET IDENTITY_INSERT [Order] ON

INSERT INTO [Order] (Id, SupplierId, DateOrdered)
VALUES
    (1, 10, '01/01/2000'),
    (2, 10, '01/01/2000')

SET IDENTITY_INSERT [Order] OFF


CREATE TABLE [OrderItem]
(
    ID INT Primary Key Identity,
    Quantity Int,
    ProductCode CHAR(2),
    Price Int,
    FKOrderId Int
)
SET IDENTITY_INSERT [OrderItem] ON

INSERT INTO [OrderItem] (Id, Quantity, ProductCode, Price, FKOrderId)
VALUES 
    (1, 20, 'X1', 100, 1),
    (2, 10, 'Y1', 50, 1),
    (3, 30, 'X1', 100, 2),
    (4, 20, 'Y1', 50, 2)

SET IDENTITY_INSERT [OrderItem] OFF

Query 1:

DECLARE @NewEntries TABLE (ID Int, OldId Int);


MERGE INTO [Order]
USING [Order] AS cf
ON 1 = 0 -- Ensure never match - therefore an Insert
WHEN NOT MATCHED AND cf.SupplierId = 10 THEN
  INSERT(SupplierId, DateOrdered) Values(cf.SupplierId, cf.DateOrdered)
Output inserted.Id, cf.Id INTO 
  @NewEntries(Id, OldId);

INSERT INTO [OrderItem]
(
   Quantity
  ,ProductCode
  ,Price
  ,FkOrderId
)
SELECT 
  Quantity
 ,ProductCode
 ,Price 
 ,NE.ID
FROM [OrderItem] OI
INNER JOIN @NewEntries NE
    ON OI.FKOrderId = NE.OldId ;


SELECT *
FROM [OrderItem];

Results:

| ID | QUANTITY | PRODUCTCODE | PRICE | FKORDERID |
|----|----------|-------------|-------|-----------|
|  1 |       20 |          X1 |   100 |         1 |
|  2 |       10 |          Y1 |    50 |         1 |
|  3 |       30 |          X1 |   100 |         2 |
|  4 |       20 |          Y1 |    50 |         2 |
|  5 |       20 |          X1 |   100 |         3 |
|  6 |       10 |          Y1 |    50 |         3 |
|  7 |       30 |          X1 |   100 |         4 |
|  8 |       20 |          Y1 |    50 |         4 |

Answer 2

You could try doing and inner join between Order and OrderItems where the clause of the inner join is SupplierId = 10, or just modify your where to achieve the same result. Try doing something along the lines of:

INSERT INTO OrderItem
(
   Quantity
  ,ProductCode
  ,Price
  ,FkOrderId
)
SELECT 
  Quantity
 ,ProductCode
 ,Price 
 ,FkOrderId
FROM OrderItem 
where FkOrderId in (Select Id FROM Order WHERE SupplierId = 10)

Answer 3

Add an additional column to the Order table called OriginalOrderId. Make it nullable, FK'd back to OrderId, and put an index on it. Use "INSERT INTO [Order]... SELECT ... OUTPUT INSERTED.* INTO #ClonedOrders From ...". Add an index on #ClonedOrders.OriginalOrderId. Then you can do "INSERT INTO OrderItem ... SELECT co.OrderId, ... FROM #ClonedOrders co INNER JOIN OrderItem oi ON oi.OrderId = co.OriginalOrderId". This will get you the functionality that you're looking for, along with the performance benefits of set based statements. It will also leave you evidence of the original source of the orders and a field that you can use to differentiate cloned orders from non-cloned orders.

Answer 4

This can be achieved with a cursor. But please note that cursors will pose significant performance drawbacks.

DECLARE @SupplierID AS INT
DECLARE @OrderId AS INT
DECLARE @DateOrdered AS DATE
DECLARE @OrderIdNew AS INT

Declare @Order AS Table(OrderId INT,SupplierID INT,DateOrdered Date)

INSERT INTO @Order
SELECT 
   ID
  ,SupplierID
  ,DateOrdered
FROM [Order]
WHERE SupplierId = 10

DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT 
   OrderId
  ,SupplierID
  ,DateOrdered
FROM @Order

OPEN CUR
FETCH NEXT FROM CUR INTO @OrderId, @SupplierID, @DateOrdered
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [Order]
    (
    SupplierId
   ,DateOrdered
    )
    VALUES
    (@SupplierID,@DateOrdered)

    Select  @OrderIdNew=@@IDENTITY

    INSERT INTO [OrderItem]
           ([Quantity]
           ,[ProductCode]
           ,[Price]
           ,[FkOrderId])
     SELECT [Quantity]
           ,[ProductCode]
           ,[Price]
           ,@OrderIdNew
     FROM [OrderItem]
     WHERE [FkOrderId]=@OrderId
    FETCH NEXT FROM CUR INTO @OrderId, @SupplierID, @DateOrdered
END

CLOSE CUR;
DEALLOCATE CUR;

Answer 5

This will handle your first table.

PS: Supply your questions in this state and they will be answered faster.

IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
IF OBJECT_ID('OrderItem') IS NOT NULL DROP TABLE OrderItem
IF OBJECT_ID('tempdb..#FinalOrders') IS NOT NULL DROP TABLE #FinalOrders


CREATE TABLE Orders (OrdersID INT, SupplierID INT, DateOrdered DATETIME)

CREATE TABLE OrderItem (OrderItemID INT, Quantity INT, FkOrderId INT)

INSERT INTO Orders VALUES (1,20,'01/01/2000'),(2,20,'01/01/2000')

INSERT INTO OrderItem VALUES
(1,20,1),
(2,10,1),
(3,30,2),
(4,20,2)

SELECT 
    a.OrderItemID,
    b.SupplierID,
    b.DateOrdered
INTO #FinalOrders
FROM OrderItem as a
INNER JOIN Orders as b
ON a.FkOrderId = b.OrdersID

SELECT * FROM #FinalOrders

Answer 6

in this case you have to use output clause.. let me give you one sample script that will help you to relate with your requirement

Declare @Order AS Table(id  int identity(1,1),SupplierID INT)

DECLARE @outputOrder AS TABLE 
(Orderid INT)

INSERT INTO @Order (SupplierID)
    Output inserted.id into @outputOrder
    Values (102),(202),(303)    


select * from @outputOrder

next step for your case would be use newly generated orderid from outputorder table & join to get orderitems from input table