access转换成mssql的问题-22世纪it爱好者

原创

ftjobdic 2012-12-20 20:51:01 ©著作权

文章标签 access mssql 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者ftjobdic的原创作品，请联系作者获取转载授权，否则将追究法律责任

以下内容仅代表本博客主人个人观点：

我以前用的是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

==================================================