我以前用的是access数据库,里面有个字段id,为自动编号,现在导入到mssql中了,当添加记录的时候那个id字段怎么办啊,也就是怎么能让mssql中出现自动编号这种字段?谢了!
常见问题
1,由于SQL2000里面没有"自动编号",所以你的以"自动编号"设置的字段都会变成非空的字段,这就必须手工修改这些字段,并把他的"标示"选择"是",种子为"1",增量为"1"
(备注:此法可行!!!!)
2,另外,ACCESS2000转换成SQL2000后,原来属性为"是/否"的字段将被转换成非空的"bit",这时候你必须修改成自己想要的属性了;
3,另外,大家要注意对时间函数的把握.ACCESS与SQL是有很多不同的.
*******************************************************************
*******************************************************************
MSSQL还是2000好,2005精简不好用!!!
下面的为网摘,不知是否可行!
*******************************************************************
*******************************************************************
最近忙于将ACCESS的数据库转化为SQL SERVER的数据库的学习,从中摸索出几条经验,与大家分享.
2.转化时,跟日期有关的字段,SQL SERVER默认为smalldatetime型,我们最好将它变为datetime型,因为datetime型的范围比smalldatetime型大。我遇见这种情况,用smalldatetime型时,转化失败,而用datetime型时,转化成功。
3.对此两种数据库进行操作的sql语句不全相同,例如:在对ACCESS数据库进行删除纪录时用:"delete * from user where id=10",而对SQL SERVER数据库进行删除是用:"delete user where id=10".
4.日期函数不相同,在对ACCESS数据库处理中,可用date()、time()等函数,但对
SQL SERVER数据库处理中,只能用datediff,dateadd等函数,而不能用date()、time()等函数。
5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数,像cstr()函数,而对SQL SERVER数据库处理中,却不能用
*******************************************************************
SQLServer数据库自增长标识列的更新修改操作
方法
在日常的sql server开发中,经常会用到Identity类型的标识列作为一个表结构的自增长编号。比如文章编号、记录序号等等。自增长的标识列的引用很大程度上方便了数据库程序的开发,但是有时这个固执的字段类型也会带来一些麻烦。
一、修改标识列字段的值:(在执行insert时,将ID手动的设置成想要的值)
有时,为了实现某个功能,需要修改类型为Identity自增长类型的字段的值,但由于标识的类型所限,这种操作默认是不允许的。比如,目前数据库有10条正常记录,引时删除5条,那么如果再添加数据时,自增长的标识列会自动赋值为11,可这时如果想在插入数据时给赋值6呢,默认是不允许的。如果说想改变主键标识符的值,完全由自已控制标识字段的插入,方法是有的,如下:
SET IDENTITY_INSERT [TABLE][ON|OFF]
使用上述语句,可以方便的控制某个表的某个自增长标识列是否自动增长,也就是说是否允许你在inert一条记录时手动指定主键标识列字段的值。
如果指定为ON,则可以insert时指定主键标识字段的值,该值不自动增长。如果使用完毕,还需使用这个语句将开关关闭到默认状态OFF,不然下次insert数据时该字段还是不会自动增长,要做到有始有终嘛。只是每次插入时都要手动的设置一个主键值,示列如下:
set identity_insert question_title on //开启手动设置
insert into question_title (f_id,f_valid,f_questionTitle) values(6,5,'hello'); //手动的向数据库插入数据
二、重置标识列字段值:
当数据记录被删除一部分后,后面再添加的新数据记录,标识列数值会有很大的空闲间隔,看上去不是很爽。即使你删除表中全部记录,identity标识列的值还是会无何止的自动增加变大,而不是从头开始增长。通过下面这条语句可以重置自增长字段的值:
DBCC CHECKIDENT(TABLE,[RESEED|NORESEED],[1])
上述语句把指定表的ID值(种子值)强制重设为1。然而,如果你不想将ID重设为1,你可以用你想用的ID值(种子值)替代第三个参数。如果你想知道当前的ID值(种子值),而不想重设标识种子,这时你就要用NORESEED,而不用再去设置第三个参数。
DBCC checkident(question_title,reseed,1)
insert into question_title (f_valid,f_questionTitle) values(5,'hello');
如果将表中记录都删除后,执行上面的一句话,这时ID的自增长值已被重设,再insert插入记录到数据库时,ID就可以从设置的1开始,以后每次插入的记录还是自动增长。
执行成功后提示如下:
--检查标识信息: 当前标识值 '9',当前列值 '1'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
说明:以上显示数据库某表中当前ID最大值为9,当前设置的ID值为1。
以上功能用得很少,我想也很少人知道吧。我上个星期帮一个朋友解决他做的一个网站的块排序问题,他问我能不能用update修改主键的值,他用的是主键自增长,我想这肯定是不可能的,如果想改的话就不要将主键设置为自增长,而是每次增加的时间手动插入。于是他给我看了一篇关于自增长的文章,我总结出设置为自增长的主键ID是不能被update的。
但我发现一点如果删除数据库里的记录,再次向数据库插入数据时,ID还是从删除的那条记录后面开始记数的,ID能不能重新开始自增长呢,或是接原来的数据后面增长,比如我删除了50~100记录,我要再插入时ID为51而不是101,经过测试,是可以实现的,所以总结了上面的。
*******************************************************************
发现MSSQL导入数据时标识列存在的问题,已解决!
(2008/1/11已更新,附上解决方案,可以自动修复标识列的编码规则)
最近在合并异地SQL数据的时候,发现MSSQL的标识列在合并数据时,可以启用标识插入也可以不启用标识插入,两种方法都可能存在一些潜在的问题,最严重的是导入操作改变了目的表的标识列编号规则。
场景:A地的A表,B地有B表,数据结构除了标误列的种子不一样外其它完全一样:
A(ID IDENTITY (1, 2), Content varchar(50)) 生成奇数主键ID
B(ID IDENTITY (2, 2), Content varchar(50)) 生成偶数主键ID
--A地的A表
CREATE TABLE dbo.A
(
ID int NOT NULL IDENTITY (1, 2),
Content varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.A ADD CONSTRAINT
PK_A PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
--在A表中插入两条数据:预先插入A表两条数据
INSERT INTO [dbo].[A] ([Content]) VALUES ('A1') --生成1, 'A1'
INSERT INTO [dbo].[A] ([Content]) VALUES ('A3') --生成3, 'A3'
--B地的B表
CREATE TABLE dbo.B
(
ID int NOT NULL IDENTITY (2, 2),
Content varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.B ADD CONSTRAINT
PK_B PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
--在B表中插入1条数据:预先插入B表1表数据
INSERT INTO [dbo].[B] ([Content]) VALUES ('B2') --生成2, 'B2'
现在要将A表数据导入到B表,问题出现了:
1. 不启用标识插入的问题:
不启用标识,B表会增加两条数据
4,'A1'
6,'A3'
造成同一数据在两地数据库表中的不一致情况,以及无法判断或者找出哪些数据已合并,哪些数据已修改或已删除,哪些数据重复导入等等问题。
2. 启用标识插入的问题:
启用标识后,B表会增加两条数据
1,'A1'
3,'A3'
用户最不能接受的错误出现了,B表录入的新数据是
5, '从A表导入两条数据后录入B表的新数据1'
7, '从A表导入两条数据后录入B表的新数据2'
导入合并操作改变了B表的标识列编号规则。
2008-1-11,今天终于搞定了,一个存储过程完成解决,自动修复标识列的编码规则。
上例中,调用dbo.sp_RepairIDENTITY 'B', 'ID' 即可,执行结果如下:
当前最大值:3
标识种子:2
标识增量:2
当前标识值:3
需设新标识值:4
检查标识信息: 当前标识值 '3',当前列值 '4'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
结果:已修复标识,当前标识值重设为4
代码如下:
CREATE PROCEDURE dbo.sp_RepairIDENTITY
(
@Tablename nvarchar(255), --表名
@RowID_Name nvarchar(20) --标识列名
)
as
SET NOCOUNT ON
--Select @TableName=N'B',@RowID_Name=N'ID'
--获取最大标识值MaxID
Declare @sql nvarchar(4000), @MaxID bigint
Set @sql = N'Select @MaxID =Max(' + @RowID_Name + N') from ' + @TableName
Exec sp_Executesql @sql, N'@MaxID bigint output',@MaxID output
--print @MaxID
Declare @IDENT_CURRENT bigint, @NewIDentity bigint, @IDENT_SEED int, @IDENT_INCR int
Select @IDENT_SEED = IDENT_SEED(@TableName), --标识种子
@IDENT_INCR = IDENT_INCR(@TableName), --标识增量
@IDENT_CURRENT = IDENT_CURRENT(@TableName), --当前标识值
@NewIDentity = @MaxID - @MaxID % cast(IDENT_SEED(@TableName) as int) + IDENT_INCR(@TableName) --需要设置的新标识值
Print '当前最大值:' + Cast(@MaxID as nvarchar(20))
Print '标识种子:' + Cast(@IDENT_SEED as nvarchar(20))
Print '标识增量:' + Cast(@IDENT_INCR as nvarchar(20))
Print '当前标识值:' + Cast(@IDENT_CURRENT as nvarchar(20))
Print '需设新标识值:' + Cast(@NewIDentity as nvarchar(20))
------关键代码:启用标识导入数据或手工执行过DBCC CHECKIDENT('table_name',RESEED new_reseed_value)就会造成编码规则改变,所以要修复编码规则
if(@IDENT_CURRENT < @MaxID or @IDENT_CURRENT % @IDENT_SEED >0)
begin
DBCC CHECKIDENT (@TableName, RESEED, @NewIDentity)
print '结果:已修复标识,当前标识值重设为' + Cast(@NewIDentity as nvarchar(20))
end
else
begin
Print '结果:不需要重设标识列。'
end
SET NOCOUNT OFF
*******************************************************************
mssql 如何更新标识列
update book set bookid=46080 where bookid=72708
其实bookid为自增长列,执行报如下错误”无法更新标识列 'bookid'。“
通过设置identity_insert为on依然不行。
set identity_insert booklistinfo ON;
关闭原因:
947 最佳答案
----允许对系统表进行更新
exec sp_configure 'allow updates',1
reconfigure with override
GO
----取消标识列标记
update syscolumns set colstat = 0 where id = object_id('tablename') and colstat = 1
GO
--插入id=8001-8003的行
...
----恢复标识列标记
update syscolumns set colstat = 1 where id = object_id('tablename') and name = '标识列名称'
----重新设置标识的起始值
DBCC CHECKIDENT (表名称, RESEED, 10003)
----禁止对系统表进行更新
exec sp_configure 'allow updates',0
reconfigure with override
GO
这种方法蛮繁锁的。
*******************************************************************
MSSQL ALTER TABLE 用语句修改Primary key 的ID
alter table p_w_picpath alter column id int IDENTITY (1, 1) NOT NULL
我只能上查询分析器,所以只能这么该,对马,系统提示错误,怎么该阿??
==================================================
不能直接修改为标识列:
可以先加新的标识列,再设置允许修改标识列,再用原来的字段值填充标识列,再删除原字段,再对字段改名
ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null
go
SET IDENTITY_INSERT 表 ON
go
update 表 set 编号1=编号
go
SET IDENTITY_INSERT 表 OFF
go
ALTER TABLE 表 DROP COLUMN 编号
go
exec sp_rename '表.编号1','编号'
go
==================================================
alter table [p_w_picpath] alter column [id] int IDENTITY (1,1) NOT NULL
==================================================
ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null
go
ALTER TABLE 表 DROP COLUMN 编号
go
exec sp_rename '表.编号1','编号'
go
==================================================
*******************************************************************
mssql 标识类指定值一般情况,我们想对自动增长列进行插入值是不可能的事情,但是有时就需要我们为标识列指定值。
在mssql 查询分析器中对标识列指定值的时候,就是报
当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'FilePath' 中的标识列插入显式值。
也就是说当把IDENTITY_INSERT 指定为on的时候就可以使用了, 去找一下IDENTITY_INSERT这个的用法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
好了,先测试一下
建表
create table test (
id int identity(1,1),
name varchar(200)
);
然后指定
set IDENTITY_INSERT test on
insert into test(id) values(0);
插入成功。 (注意,当 IDENTITY_INSERT 为on的时候,要为要指定要插入的列,要不然不能正常插入的。
最后把IDENTITY_INSERT 关闭,不关闭的话,以后都要为id指定值了。
set IDENTITY_INSERT test off
转载于:https://blog.51cto.com/6327685/1095551