表结构如下:
CREATE TABLE [T_MATERIA_HIS] (
[id] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[contractno] [varchar] (20) COLLATE Chinese_PRC_CI_AS ,
[step] [int] ,
[customerid] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[amount] [decimal] (8,2) NOT NULL ,
[money] [decimal] (8,2) NOT NULL ,
[tmoney] [decimal] (12,2) ,
[inoutflag] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[warehouse] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[resp] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[ad] [varchar] (1) COLLATE Chinese_PRC_CI_AS ,
[procdatetime] [datetime] NOT NULL ,
[procer] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[addatetime] [datetime] ,
[ader] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[memo] [varchar] (80) COLLATE Chinese_PRC_CI_AS ,
[auditre] [varchar] (120) COLLATE Chinese_PRC_CI_AS ,
[updatetime] [datetime] NOT NULL ,
[updateuser] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[lastchanged] [timestamp] NOT NULL ,
CONSTRAINT [PK_T_MATERIA_HIS] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
现在我想在列[customerid]之后插入一列provider varchar(10)
但是我用SQL语句
alter table T_MATERIA_HIS
add provider varchar(10) 插入列provider 之后,它却列在表的最后一列。
请问应如何修改上面的SQL语句,使列provider 插入列[customerid]之后?
CREATE TABLE [T_MATERIA_HIS] (
[id] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[contractno] [varchar] (20) COLLATE Chinese_PRC_CI_AS ,
[step] [int] ,
[customerid] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[amount] [decimal] (8,2) NOT NULL ,
[money] [decimal] (8,2) NOT NULL ,
[tmoney] [decimal] (12,2) ,
[inoutflag] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[warehouse] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[resp] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[ad] [varchar] (1) COLLATE Chinese_PRC_CI_AS ,
[procdatetime] [datetime] NOT NULL ,
[procer] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[addatetime] [datetime] ,
[ader] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[memo] [varchar] (80) COLLATE Chinese_PRC_CI_AS ,
[auditre] [varchar] (120) COLLATE Chinese_PRC_CI_AS ,
[updatetime] [datetime] NOT NULL ,
[updateuser] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[lastchanged] [timestamp] NOT NULL ,
CONSTRAINT [PK_T_MATERIA_HIS] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
现在我想在列[customerid]之后插入一列provider varchar(10)
但是我用SQL语句
alter table T_MATERIA_HIS
add provider varchar(10) 插入列provider 之后,它却列在表的最后一列。
请问应如何修改上面的SQL语句,使列provider 插入列[customerid]之后?
然後再把後面的補上
指定列位置是不行的吧,也没必要阿
1.新建临时表.
CREATE TABLE [TMP_T_MATERIA_HIS] (
[id] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[contractno] [varchar] (20) COLLATE Chinese_PRC_CI_AS ,
[step] [int] ,
[provider] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[customerid] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[amount] [decimal] (8,2) NOT NULL ,
[money] [decimal] (8,2) NOT NULL ,
[tmoney] [decimal] (12,2) ,
[inoutflag] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[warehouse] [varchar] (10) COLLATE Chinese_PRC_CI_AS ,
[resp] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[ad] [varchar] (1) COLLATE Chinese_PRC_CI_AS ,
[procdatetime] [datetime] NOT NULL ,
[procer] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[addatetime] [datetime] ,
[ader] [varchar] (7) COLLATE Chinese_PRC_CI_AS ,
[memo] [varchar] (80) COLLATE Chinese_PRC_CI_AS ,
[auditre] [varchar] (120) COLLATE Chinese_PRC_CI_AS ,
[updatetime] [datetime] NOT NULL ,
[updateuser] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[lastchanged] [timestamp] NOT NULL ,
CONSTRAINT [PK_T_MATERIA_HIS] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
2.把旧表T_MATERIA_HIS的数据导入临时表TMP_T_MATERIA_HIS
3.drop table T_MATERIA_HIS
4.重命名临时表sp_rename
2,需要查系统表syscolumns找到 b 列,记下colid 设为 @currentColId
3,再将当前表的列在syscolumns里的记录colid>@currentColId的colid递加.
并将新加入的列的colid设置为@currentColId.并且,要考滤syscolumns表里当前表的列的列序是否连续的.
另外,系统表默认是不允许更改的,要用sp_config 设置改变allow updates 属性后才能更改. 改完列序(colid)后再改回来.