--常用的东西以下“彩色”部分,是要修改部分
--检查表是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_paper_info_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test_paper_info_temp]
GO
--新建一个和原表结构相同的表,下面的结构可以直接从数据表生成SQL脚本中获得
--注意:下面这行最后的表名要加上 _temp
CREATE TABLE [dbo].[test_paper_info_temp] (
[paper_id] [int] NOT NULL ,
[questionid] [int] NOT NULL ,
[selectid] [int] NULL ,
[classid] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[classname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[qname] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice1] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice2] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice3] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice4] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice5] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[choice6] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[answer] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[timu_type] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[type_score] [int] NULL
) ON [PRIMARY]
GO
--新建一个临时表的索引,并“忽略重复的值”,xxx作为唯一表识值字段
CREATE UNIQUE INDEX [temp] ON [dbo].[test_paper_info_temp]([paper_id]) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO
--把原表的数据导入临时表,XXX为原表名
--insert into test_paper_info_temp Select * from test_paper_info
insert into test_paper_info_temp Select * test_paper_info_info
--清空原表,并将临时表中的数据导回原表,最后删除临时表
delete test_paper_info
insert into test_paper_info select * from test_paper_info_temp
drop table test_paper_info_temp
相关文章