使用游标进行循环数据插入

USE  test
GO

IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' Orders ' )
     DROP   TABLE  Orders
    
IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' Customers ' )
     DROP   TABLE  Customers
    
/*
    Customer 1 --> n Order
*/

--  Create Customer Table
CREATE   TABLE  Customers
(
    CustomerId  UNIQUEIDENTIFIER   PRIMARY   KEY ,
    Name  VARCHAR ( 25 ),
)
GO


--  Create Order Table
CREATE   TABLE  Orders
(
    OrderId  UNIQUEIDENTIFIER   PRIMARY   KEY ,
    Name  VARCHAR ( 255 ),
    Customer_Id  UNIQUEIDENTIFIER   FOREIGN   KEY   REFERENCES  Customers(CustomerId)
)
GO


--  Create Order History Table
IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' OrderHistory ' )
     DROP   TABLE  OrderHistory

CREATE   TABLE  OrderHistory
(
    OrderHistoryId  UNIQUEIDENTIFIER   PRIMARY   KEY   DEFAULT ( NEWID ()),
    CustomerName  VARCHAR ( 25 ),
    OrderName  VARCHAR ( 255 )
)
GO

--  use cursor to insert data into order history table

DECLARE   @customer_name   VARCHAR ( 25 )
DECLARE   @order_name   VARCHAR ( 255 )

DECLARE  curOrder  CURSOR  READ_ONLY
FOR
SELECT  c.Name  as   [ Customer Name ] , o.Name  as   [ Order Name ]
FROM  Customers c  INNER   JOIN  Orders o
     ON  c.CustomerId  =  o.Customer_Id
ORDER   BY   [ Customer Name ] ,  [ Order Name ]

OPEN  curOrder

FETCH   NEXT   FROM  curOrder
INTO   @customer_name ,  @order_name

WHILE   @@FETCH_STATUS   =   0     
BEGIN
     EXEC  spAddOrderHistory  @customer_name ,  @order_name  
           
     FETCH   NEXT   FROM  curOrder  INTO   @customer_name ,  @order_name
END

CLOSE  curOrder
DEALLOCATE  curOrder