SQL Server中Insert与Select的组合使用

上一篇 / 下一篇  2008-03-10 14:42:00 / 个人分类:SQL

 

SQL Server中有示例数据库Northwind。新建表Sales用于存储销售信息,字段为EmployeeID,ProductID,SupplierID,CustomerID,OrderDate,UnitPrice,Total,Quantity,Discount。

下面的语句从Orders,Order Details, Employees,Products, Suppliers, Customers表选择相应的

数据插入Sales表:

insert into Sales(EmployeeID,ProductID,SupplierID,CustomerID,
OrderDate,UnitPrice,Total,Quantity,Discount) 
select e.EmployeeID, p.ProductID, s.SupplierID,
c.CustomerID, o.OrderDate, od.UnitPrice,
od.Quantity*od.UnitPrice*(1.0-od.Discount)Total,
Od.Quantity, od.Discount
from Orders o,[Order Details] od, Employees e,
Products p, Suppliers s, Customers c
where (o.OrderID = od.OrderID) and
(o.EmployeeID = e.EmployeeID) and
(o.CustomerID = c.CustomerID) and
(od.ProductId = p.ProductID) and
(p.SupplierID = s.SupplierID);
go

TAG: SQL Server

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

日历

« 2008-07-04  
  12345
6789101112
13141516171819
20212223242526
2728293031  

数据统计

  • 访问量: 1153
  • 日志数: 90
  • 建立时间: 2007-04-03
  • 更新时间: 2008-04-07

RSS订阅

Open Toolbar