现在我要将数据库所有表的IDENTITY_INSERT设置为ON
用来升级程序与数据库
数据导完后又设为IDENTITY_INSERT OFF采取游标处理DECLARE AllTables CURSOR FORWITH TableHasidentity AS(
SELECT *
FROM sys.columns
WHERE is_identity=1
)
SELECT SYSOBJECTS.[name]
FROM SYSOBJECTS ,TableHasidentity
WHERE SYSOBJECTS.id = TableHasidentity.object_id and type='u'OPEN AllTables
declare @TableName nvarchar(100)
FETCH NEXT FROM AllTables into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET IDENTITY_INSERT '+@TableName+' ON')
FETCH NEXT FROM AllTables into @TableName
END
CLOSE AllTables
执行命令的时候
消息提示一条记录‘命令已成功完成’
我那个语法写的不对吗?
游标用的很少 ,急用 谢谢了。
我的数据库是Sqlserver2005
用来升级程序与数据库
数据导完后又设为IDENTITY_INSERT OFF采取游标处理DECLARE AllTables CURSOR FORWITH TableHasidentity AS(
SELECT *
FROM sys.columns
WHERE is_identity=1
)
SELECT SYSOBJECTS.[name]
FROM SYSOBJECTS ,TableHasidentity
WHERE SYSOBJECTS.id = TableHasidentity.object_id and type='u'OPEN AllTables
declare @TableName nvarchar(100)
FETCH NEXT FROM AllTables into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET IDENTITY_INSERT '+@TableName+' ON')
FETCH NEXT FROM AllTables into @TableName
END
CLOSE AllTables
执行命令的时候
消息提示一条记录‘命令已成功完成’
我那个语法写的不对吗?
游标用的很少 ,急用 谢谢了。
我的数据库是Sqlserver2005
SELECT 'SET IDENTITY_INSERT ['+object_name(object_id)+'] ON'
FROM sys.columns
WHERE is_identity=1
OPEN AllTables
declare @s nvarchar(1000)
FETCH NEXT FROM AllTables into @s
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@s)
FETCH NEXT FROM AllTables into @s
END
CLOSE AllTables
DEALLOCATE AllTables
DECLARE AllTables CURSOR FORWITH TableHasidentity AS(
SELECT *
FROM sys.columns
WHERE is_identity=1
)
SELECT SYSOBJECTS.[name]
FROM SYSOBJECTS ,TableHasidentity
WHERE SYSOBJECTS.id = TableHasidentity.object_id and type='u'OPEN AllTables
declare @TableName nvarchar(100)
FETCH NEXT FROM AllTables into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET IDENTITY_INSERT '+@TableName+' ON')
FETCH NEXT FROM AllTables into @TableName
END
CLOSE AllTables
DEALLOCATE AllTables
SELECT 'SET IDENTITY_INSERT ['+a.name+'] ON'
FROM sys.columns a
join sys.objects b
on a.object_id=b.object_id
WHERE is_identity=1 and b.type='u'
OPEN AllTables
declare @s nvarchar(1000)
FETCH NEXT FROM AllTables into @s
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@s)
FETCH NEXT FROM AllTables into @s
END
CLOSE AllTables
DEALLOCATE AllTables改为这样。
DECLARE AllTables CURSOR FOR
SELECT 'SET IDENTITY_INSERT ['+b.name+'] ON'
--a.name 改b.name 查询是所有设置命令都有了,实际上没执行所有啊
FROM sys.columns a
join sys.objects b
on a.object_id=b.object_id
WHERE is_identity=1 and b.type='u'
OPEN AllTables
declare @s nvarchar(1000)
FETCH NEXT FROM AllTables into @s
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@s)
FETCH NEXT FROM AllTables into @s
END
CLOSE AllTables
DEALLOCATE AllTables--a.name 改b.name 查询是所有设置命令都有了,实际上没执行所有啊
消息就提示一个 “命令已成功完成”
好像没执行成功
我直接 在游标查询中 指定 “表名”就执行一个表的 'SET IDENTITY_INSERT ON
也没效果