第八章 数据修改(1)

--第8章 数据修改
--8.1 插入数据
--8.1.1 INSERT VALUES 语句
use tempdb;
if OBJECT_ID('dbo.Orders', 'U') is not null
drop table dbo.Orders;

create table dbo.Orders
(
orderid int not null
constraint PK_Orders primary key,
orderdate date not null
constraint SFT_Orderdate Default(Current_timestamp),
empid int not null,
custid varchar(10) not null
);

insert into dbo.Orders(orderid, orderdate, empid, custid)
values(10001, N'20090212', 3, 'A');

insert into dbo.Orders(orderid, empid, custid)
values(10002, 2, 'C');

--SQL Server2008增强了Values的语句功能，允许在一条语句中指定由逗号分隔开的多行记录
insert into dbo.Orders(orderid, orderdate, empid, custid)
values
(1003, N'20001212', 2, 'D'),
(1004, N'20121212', 2, 'B');

--SQL Server2008增强了Values子句本身的功能,现在可以用它来构建虚拟表.
select * from 
(values
(1003, N'20001212', 2, 'D'),
(1004, N'20121212', 2, 'B'))
as o(orderid, orderdate, empid, custid);

--8.1.2 Insert Select语句
insert into dbo.Orders(orderid, orderdate, empid, custid)
select orderid, orderdate, empid, custid
from TSQLFundamentals2008.Sales.Orders
where shipcountry = N'UK';

select * from dbo.Orders;

--8.1.3 Insert Exec语句
use TSQLFundamentals2008;
go

if OBJECT_ID('Sales.usp_getorders', 'p') is not null
drop proc sales.usp_getorders;
go

create proc Sales.usp_getorders
@country as NVARCHAR(40)
as
select orderid, orderdate, empid, custid
from Sales.Orders
where shipcountry = @country;
go

exec Sales.usp_getorders @country = N'France'

use tempdb;
go

insert into dbo.Orders(orderid, orderdate, empid, custid)
exec TSQLFundamentals2008.Sales.usp_getorders @country = N'France';

--8.1.4 Select Into语句
--Select into语句的作用是创建一个目标表,并且查询返回的结果来填充它.Select into语句不是一个标准的SQL语句.
use tempdb;
go

if OBJECT_ID('dbo.Orders', 'U') is not null
drop table dbo.orders;

select orderid, orderdate, empid, custid
into dbo.orders
from TSQLFundamentals2008.Sales.Orders;

--Select into语句不会从来源表中复制3样东西：约束、索引及触发器
--如果想使用带有集合操作的select into语句，应该把into子句放在第一个查询的from子句之前。
if OBJECT_ID('dbo.Locations', 'U') is not null
drop table dbo.Locations;

select country, region, city
into dbo.locations
from TSQLFundamentals2008.Sales.Customers
except
select country, region, city
from TSQLFundamentals2008.HR.Employees