建表语句:
代码
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
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
(
@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
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