1. 原始表(T_B_PosDish)结构如下:
2. 为该表添加一个fID字段,从1开始自增,且设置主键,语句如下:
Alter table T_B_PosDish Add fID int primary key Identity(1, 1) not null
效果如下:
这个列追加到了最后, 由于某些原因,需要吧fID添加到第一列。又由于某些原因,不能在SQL管理器去设计表,只能通过SQL语句进行。
3. 考虑该表数据不能丢失,执行了以下语句(删除了之前表添加的fID列):
a. 转移数据至备份表T_B_PosDish_Bak
b. 删除T_B_PosDish表
c. 新建T_B_PosDish表
d. 将备份表T_B_PosDish_Bak的数据转到到T_B_PosDish表
e. 删除备份表T_B_PosDish_Bak
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[T_B_PosDish]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
if (select id from syscolumns where name='fID' and [id]=object_id('T_B_PosDish')) is null
begin
select * into T_B_PosDish_Bak from T_B_PosDish
Drop Table T_B_PosDish
CREATE TABLE [dbo].[T_B_PosDish](
[fID] [int] IDENTITY(1,1) NOT NULL,
[fPosID] [varchar](5) NOT NULL,
[fKeyCode] [int] NOT NULL,
[fDishID] [varchar](20) NOT NULL,
CONSTRAINT [PK_T_B_PosDish] PRIMARY KEY CLUSTERED
([fID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into T_B_PosDish(fPosID,fKeyCode,fDishID) select * from T_B_PosDish_Bak
drop table T_B_PosDish_Bak
END
END
Go
效果如下:
原始表的数据也没有丢失。
个人整理,仅供参考!