使用identity_insert后如何自动重新设置种子? [英] How to automatically reseed after using identity_insert?

问题描述

我最近从PostgreSQL数据库迁移到了SQL Server数据库.要切换数据，我必须启用IDENTITY_INSERT.好吧，我发现在任何一个表中进行插入时，由于重复的标识值(被设置为主键)，我都会遇到各种各样的奇怪错误.

I recently migrated from a PostgreSQL database to a SQL Server database. To switch the data over I had to enable IDENTITY_INSERT. Well come to find out that I get all sorts of strange errors due to duplicate identity values(which are set as primary keys) upon doing an insert in any of the tables.

我有很多桌子.自动重新播种每个表的身份以使其在max(RID)之后的最简单方法是什么?

I have quite a few tables. What would be the easiest way of automatically reseeding the identity of every table so that it is after max(RID)?

推荐答案

使用

Use the information in this link in combination with a SQL function that gets the max(RID) from each table that you need to reset. For instance, if you want to start your primary key seed at 25000, use the code below (StartSeedValue - 1)

DBCC CHECKIDENT('myTable', RESEED, 24999)

因此，结合起来，您应该得到这样的想法

So in combination, you should end up with somethink like this

DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(ID),0)+1 from mytable
DBCC CHECKIDENT('mytable', RESEED, @maxVal)

对于伪代码很抱歉，因为我已经编写了一个SQL函数:)

Sorry for the Pseudo-code, been awhile since I have written a SQL function :)

多谢了，将INTEGER更改为INT

Thanks for the catch, changed the INTEGER to INT

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO 

这将为您提供数据库中所有用户表的列表.将此查询用作您的循环"，这应该允许重置所有表上的种子.

This will give you a listing of all user tables in the database. Use this query as your 'loop' and that should allow to reset the seeds on all tables.

这篇关于使用identity_insert后如何自动重新设置种子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！