增（INSERT）

IF OBJECT_ID('student2','U') IS NOT NULLDROP TABLE student2create 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 NULLDROP TABLE student3create 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 NULLDROP TABLE student4create 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 student2select * from student3 select * from student4select * from student5

 

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