使用游标进行循环数据插入(转载)

时间：2011-04-02 来源：dfine.sqa

建表语句:

代码

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

插入数据的存储过程:

CREATE PROCEDURE spAddOrderHistory
(
    @CustomerName VARCHAR(25),
    @OrderName VARCHAR(255)
)
AS
BEGIN
    INSERT INTO OrderHistory(CustomerName,OrderName)
    VALUES(@CustomerName, @OrderName)
END

使用游标进行数据插入:

代码 -- 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

运行结果:

C2 O2 by C2
C1 O3 by C1
C2 O4 by C2
C1 O1 by C1
C1 O5 by C1
C2 O6 by C2

相关阅读更多 +