INSERT

增（INSERT）

IF OBJECT_ID('student2','U') IS NOT NULL
DROP TABLE student2
create table student2
(
sid int primary key identity(20,5),
sname nchar(8) not null,
ssex nchar(1) constraint DF_SEX DEFAULT('女')
)
insert into student2(sname,ssex) values ('张三','男');
insert into student2(ssex,sname) values ('男','钱一');--可以打乱字段顺序，但值必须与字段一一对应。
insert into student2 values ('李四','女');--可以省略列名，但是赋值必须按照字段顺序。
insert into student2 values ('王五','女'),('周六','男');  --用values 插入多列。
insert into student2 select '吴起','男';                   --用select 插入列
insert into student2 select '郑八','女' union select '关九','男' --用select 插入多列
SET IDENTITY_INSERT  CESHI.DBO.student2 ON;
/*insert into student2 values ('9','李四','女'); */       --报错
insert into student2 (sid,sname,ssex)values ('9','李四','女');  --'9'虽为字符串型，但是可以转化为int型 
insert into student2 (sid,sname,ssex)values (12,'李四',default);  --使用default来代替默认值    
SET IDENTITY_INSERT  CESHI.DBO.student2 Off;

select * from student2

 

 

 

IF OBJECT_ID('student3','U') IS NOT NULL
DROP TABLE student3
create table student3     --创建表3与表2一致
(
sid int primary key identity(20,5),
sname nchar(8) not null,
ssex nchar(1)
)

IF OBJECT_ID('student4','U') IS NOT NULL
DROP TABLE student4
create table student4    --创建表4与表2除自增以外，其他一致
(
sid int primary key ,
sname nchar(8) not null,
ssex nchar(1)
)
go 

SET IDENTITY_INSERT  CESHI.DBO.student3 ON;    --如果student3没有自增限制，则无需执行此操作
--同一时间只能打开一个‘IDENTITY_INSERT’。如果此时执行 set IDENTITY_INSERT  student4 on  则会报错。
insert into student3(ssex ,sid ,sname ) select ssex ,sid ,sname from student2 
set IDENTITY_INSERT  student3 off     

insert into student4  select * from student2 

select * into student5 from student2  --创建新表5，将表2所有内容传入。


select * from student2
select * from student3 
select * from student4
select * from student5

 

 student2、 student3、 student4、student5查询结果一致。