alter table table_name
alter column column_name int null
go
alter column column_name int null
go
解决方案 »
- 请问安装 SQL Express2008时,只安装Framework4.0是否可以
- sql查询
- 有关按类别查出前几位的数据
- test
- 不安装客户端可以吗?
- 很简单的折扣问题:
- 【【【【【【一个简单的LEFT JOIN问题,很快结贴,希望关注
- Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'update_procure'. The st
- 不用关系图如何去掉两表之间的联系?
- 企业管理器中打开本地服务器后,SQL SERVER 2000 服务器自动停止.急急急!
- 各位,给几个数据库开发的网址吧,我实在受不了了,这里太闷了!!!!!!!!!!!!!!!!!!!!(先给先送分,立竿见影哦)
- Oracle JDBC 连接的问题(200分)
如果不信你就是试验一下
CREATE TABLE doc_exc ( column_a INT not null identity)
alter table doc_exd
alter column column_a int not null
SET IDENTITY_INSERT test ON
INSERT
INTO test(col1, col2, id111)
VALUES (3, 'e', 2)
其中id111为IDENTITY
我想这样也可以满足你的要求吧
我的方法是设置
set identity_insert doc_exd
go
insert into doc_exd(column_a)
values(1)
比较完整的的例子
-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GOSELECT *
FROM products
GO-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GOSELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
这个方法我以前已试过,理论上应该行得通,但实际上可能会有操作上的问题,
我就想不通,为什么在Enterprise Manager,点击一下就可解决的问题,而用sql语句就没办法设置,是微软在偷懒呢,还是我们不懂!!!谢谢楼上的每一位朋友,我觉得这个问题大家可以再想想,我也在努力的查资料
对一个只有字段(i int,s char)的表,
增加设置identity属性:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_000
(
i int NOT NULL IDENTITY (1, 1),
s char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_000 ON
GO
IF EXISTS(SELECT * FROM dbo.[000])
EXEC('INSERT INTO dbo.Tmp_000 (i, s)
SELECT i, s FROM dbo.[000] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_000 OFF
GO
DROP TABLE dbo.[000]
GO
EXECUTE sp_rename N'dbo.Tmp_000', N'000', 'OBJECT'
GO
COMMIT
取消identity属性:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_000
(
i int NOT NULL,
s char(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.[000])
EXEC('INSERT INTO dbo.Tmp_000 (i, s)
SELECT i, s FROM dbo.[000] TABLOCKX')
GO
DROP TABLE dbo.[000]
GO
EXECUTE sp_rename N'dbo.Tmp_000', N'000', 'OBJECT'
GO
COMMIT
如果表中的字段有默认值还更麻烦,所以微软并没有偷懒的。其实你看看Enterprise Manager生成的脚本就知道了。
但一个问题是 微软有没有做一个sql扩展,用一句sql语句就可以去掉indentity属性,
还有就是Enterprise Manager 中的脚本怎么看到的,我业余了。
我在想如果微软偷懒没做,是不是可以写一个通用的存储过程,可以修改任何表的identity 属性
毕竟我还是学到了不少
wwl007(疑难杂症) 也出了不少力,剩下的5分就给你吧