- SQL code
CREATE TABLE M_NAMES ( ID INT IDENTITY(1, 1), --ID CLASS_CD TINYINT NOT NULL, --暘椶 NAME_CD SMALLINT NOT NULL, --嬫暘 FULL_NAME NVARCHAR(60), --柤徧 SHORT_NM1 NVARCHAR(10), --棯徧1 SHORT_NM2 NVARCHAR(20), --棯徧2 REMARK1 NVARCHAR(40), --旛峫1 REMARK2 NVARCHAR(40), --旛峫2 DIV1 NVARCHAR(2), --嬫暘1 DIV2 NVARCHAR(3), --嬫暘2 DIV3 NVARCHAR(4), --嬫暘3 INS_DATE DATETIME, --搊榐擔 UPD_DATE DATETIME, --峏怴擔 VERSION INT NOT NULL, --僶乕僕儑儞 CONSTRAINT PK_NAMES PRIMARY KEY (ID), CONSTRAINT U_NAMES UNIQUE (CLASS_CD,NAME_CD))CREATE INDEX INDEX_NAME ON M_NAMES(CLASS_CD,NAME_CD)
怎么把这个id的 自增长去掉。去掉后在加上。求两行代码。
一行去自增长，一行在加上自增长（id）
------解决方案--------------------
ALTER TABLE TB DROP COLUMN ID
ALTER TABLE TB ADD ID INT IDENTITY(1,1)
?
------解决方案--------------------
- SQL code
SET IDENTITY_INSERT [TB_NAME] on--把Identity属性停用后，insert 语句要插入ID信息....
------解决方案--------------------
update tb_name set col_type=col_identity
------解决方案--------------------
SET IDENTITY_INSERT tb_name on
插入记录
SET IDENTITY_INSERT tb_name off
------解决方案--------------------
- SQL code
--在网上查了很多资料 基本上就是这样了drop table m_namesCREATE TABLE M_NAMES ( AID INT IDENTITY(1, 1), --不使用ID 是因为与下面查询的系统列名有混淆 CLASS_CD TINYINT NOT NULL, --暘椶 NAME_CD SMALLINT NOT NULL, --嬫暘 FULL_NAME NVARCHAR(60), --柤徧 CONSTRAINT PK_NAMES PRIMARY KEY (AID), CONSTRAINT U_NAMES UNIQUE (CLASS_CD,NAME_CD))insert into m_names values(1,12343,'211211')insert into m_names values (1,12,'aaad')insert into m_names values(22,4,'aa')select * from m_names--下面是重点 查询AID列系统配置信息drop table abcselect * into abc from (select name,id, typestat,colstat,autoval from syscolumns where name='AID')as b--修改AID列属性exec sp_configure 'show advanced options',1reconfiguregoexec sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO --取消自动增长update syscolumns set typestat =0 ,colstat=0 ,autoval =null where id=(select id from abc)--恢复自动增长功能--update syscolumns set typestat =(select typestat from abc),colstat=(select colstat from abc),autoval=(select autoval from abc) where id=(select id from abc )go sp_configure 'allow updates', 0 GO RECONFIGURE WITH OVERRIDE GO --恢复自动增长功能时 添加数据要这样添加set identity_insert m_names on--必加这一行 要不热就报错insert into m_names(class_cd,name_cd,full_name) values(8,7,'aba')