插入数据并删除重复

方法一：

IF OBJECT_ID('Student') IS NOT NULL DROP TABLE student 

--如果表不是空的那么删除表

CREATE TABLE Student (Sno INT,Sname VARCHAR(4),Ssex VARCHAR(2),Sage INT,sdept VARCHAR(2))

--创建表Student
INSERT INTO [学生表]
SELECT 95001,'李勇','男',20,'CS' UNION ALL
SELECT 95002,'刘晨','女',19,'IS' UNION ALL
SELECT 95003,'王敏','女',18,'IS' UNION ALL
SELECT 95004,'张立','男',19,'MA' UNION ALL
SELECT 96001,'徐一','男',20,'IS' UNION ALL
SELECT 96002,'张三','女',21,'CS' UNION ALL
SELECT 96003,'李四','男',18,'IS'

--插入记录

 

方法二：

with 学生表1 as
(
SELECT 95001,'李勇','男',20,'CS' UNION ALL
SELECT 95002,'刘晨','女',19,'IS' UNION ALL
SELECT 95003,'王敏','女',18,'IS' UNION ALL
SELECT 95004,'张立','男',19,'MA' UNION ALL
SELECT 96001,'徐一','男',20,'IS' UNION ALL
SELECT 96002,'张三','女',21,'CS' UNION ALL
SELECT 96003,'李四','男',18,'IS'
)
insert into 学生表 select * from 学生表1

方法三：

declare @Student table (Sno int,Sname varchar(4),Ssex varchar(2),Sage int,sdept varchar(2))
insert into @Student
select 95001,'李勇','男',20,'CS' union all
select 95002,'刘晨','女',19,'IS' union all
select 95003,'王敏','女',18,'IS' union all
select 95004,'张立','男',19,'MA' union all
select 96001,'徐一','男',20,'IS' union all
select 96002,'张三','女',21,'CS' union all
select 96003,'李四','男',18,'IS'

select * from @Student

alter table Student add id int identity(1,1)  --增加一个自增字段，使表具有一个唯一字段

delete from Student where  id not in(select max(id) from Student group by Sname)--删除重复

alter table Student drop column id --删除自增字段，恢复原表结构

select * from Student order by Sno;--根据编号排序显示