Sql как изменить значение identity

Вам нужно

set identity_insert YourTable ON

Затем удалите свою строку и снова вставьте ее с другим идентификатором.

После того, как вы сделали вставку, не забудьте выключить identity_insert

set identity_insert YourTable OFF

--before running this make sure Foreign key constraints have been removed that reference the ID. 

--set table to allow identity to be inserted
SET IDENTITY_INSERT yourTable ON;
GO
--insert everything into a temp table
SELECT * 
INTO #tmpYourTable
FROM yourTable

--clear your table
DELETE FROM yourTable
--insert back all the values with the updated ID column
INSERT INTO yourTable (IDCol, OtherCols)
SELECT ID+1 as updatedID --put any other update logic to the ID here
, OtherCols FROM #tmpYourTable
--drop the temp table
DROP TABLE #tmpYourTable
--put identity back to normal
SET IDENTITY_INSERT yourTable OFF;
GO

Попробуйте использовать DBCC CHECKIDENT:

DBCC CHECKIDENT ('YourTable', RESEED, 1);

DBCC CHECKIDENT(table_name, RESEED, value)

Table_name = укажите таблицу, значение которой вы хотите сбросить

Value = начальное значение должно быть нулевым, чтобы начать столбец идентификатора с 1

Скопируйте вашу таблицу в новую таблицу без столбца идентификации.

    select columns into newtable from yourtable

Добавить столбец идентификатора в новую таблицу с новым семенем и сделать его первичным ключом

    ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY

SET IDENTITY_INSERT dbo.TableName ON
INSERT INTO dbo.TableName 
(
    TableId, ColumnName1, ColumnName2, ColumnName3
)
VALUES
(
    TableId_Value, ColumnName1_Value, ColumnName2_Value, ColumnName3_Value
)

SET IDENTITY_INSERT dbo.TableName OFF

При использовании Identity_Insert не забудьте включить имена столбцов, потому что sql не позволит вам вставить без их указания

Вы также можете использовать SET IDENTITY INSERT, чтобы вставить значения в столбец идентичности.

Примере:

SET IDENTITY_INSERT dbo.Tool ON
GO

А затем вы можете вставить в столбец идентичности нужные вам значения.

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'
update tablename set newcolumnname=value where condition

Однако приведенный выше код работает только в том случае, если нет отношения первичного и внешнего ключа.

Полное решение для программистов на C # с использованием построителя команд

Прежде всего, вы должны знать следующие факты:

• В любом случае вы не можете изменить столбец идентификатора, поэтому вам нужно удалить строку и повторно добавить с новым идентификатором.
• Вы не можете удалить свойство идентификатора из столбца (вам придется удалить его в столбец)
• Конструктор настраиваемых команд из .net всегда пропускает столбец идентификаторов, поэтому вы не можете использовать его для этой цели.

Итак, однажды зная это, вам нужно сделать следующее. Либо запрограммируйте свой собственный оператор вставки SQL, либо запрограммируйте собственный построитель команд вставки. Или используйте тот, который я запрограммировал для вас. Учитывая DataTable, генерирует сценарий SQL Insert:

public static string BuildInsertSQLText ( DataTable table )
{
    StringBuilder sql = new StringBuilder(1000,5000000);
    StringBuilder values = new StringBuilder ( "VALUES (" );
    bool bFirst = true;
    bool bIdentity = false;
    string identityType = null;

    foreach(DataRow myRow in table.Rows) 
    {
        sql.Append( "rnINSERT INTO " + table.TableName + " (" );

        foreach ( DataColumn column in table.Columns )
        {
            if ( column.AutoIncrement )
            {
                bIdentity = true;

                switch ( column.DataType.Name )
                {
                    case "Int16":
                        identityType = "smallint";
                        break;
                    case "SByte":
                        identityType = "tinyint";
                        break;
                    case "Int64":
                        identityType = "bigint";
                        break;
                    case "Decimal":
                        identityType = "decimal";
                        break;
                    default:
                        identityType = "int";
                        break;
                }
            }
            else
            {
                if ( bFirst )
                    bFirst = false;
                else
                {
                    sql.Append ( ", " );
                    values.Append ( ", " );
                }
                sql.Append ("[");
                sql.Append ( column.ColumnName );
                sql.Append ("]");

                //values.Append (myRow[column.ColumnName].ToString() );

                if (myRow[column.ColumnName].ToString() == "True")
                    values.Append("1");
                else if (myRow[column.ColumnName].ToString() == "False")
                    values.Append("0");
                else if(myRow[column.ColumnName] == System.DBNull.Value)    
                    values.Append ("NULL");
                else if(column.DataType.ToString().Equals("System.String"))
                {
                    values.Append("'"+myRow[column.ColumnName].ToString()+"'");
                }
                else
                    values.Append (myRow[column.ColumnName].ToString());
                    //values.Append (column.DataType.ToString() );
            }
        }
        sql.Append ( ") " );
        sql.Append ( values.ToString () );
        sql.Append ( ")" );

        if ( bIdentity )
        {
            sql.Append ( "; SELECT CAST(scope_identity() AS " );
            sql.Append ( identityType );
            sql.Append ( ")" );
        }
        bFirst = true;
        sql.Append(";");
        values = new StringBuilder ( "VALUES (" );
    } //fin foreach
    return sql.ToString ();
}

Я решил эту проблему сначала с помощью DBCC, а затем с помощью вставки. Например, если ваша таблица

Сначала установите новое текущее значение идентификатора в таблице как NEW_RESEED_VALUE

MyTable {IDCol, colA, colB}

    DBCC CHECKIDENT('MyTable', RESEED, NEW_RESEED_VALUE)

Тогда вы можете использовать

    insert into MyTable (colA, ColB) select colA, colB from MyTable

Это приведет к дублированию всех ваших записей, но с использованием нового значения IDCol, начиная с NEW_RESEED_VALUE. Затем вы можете удалить повторяющиеся строки с более высоким значением идентификатора после того, как вы удалили / переместили их ссылки на внешние ключи, если таковые имеются.

Вы можете создать новую таблицу, используя следующий код.

SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable

Затем удалите старую базу данных и переименуйте новую базу данных в имя старой базы данных. Примечание : столбцы column1 и column2 представляют все столбцы в вашей старой таблице, которые вы хотите сохранить в новой таблице.

Если вам конкретно нужно изменить значение первичного ключа на другое число (например, 123 -> 1123). Свойство identity блокирует изменение значения PK. Установить Identity_insert не получится. Выполнение вставки / удаления не рекомендуется, если у вас есть каскадные удаления (если вы не отключили проверку ссылочной целостности).

РЕДАКТИРОВАТЬ: более новые версии SQL не позволяют изменять сущность syscolumns, поэтому часть моего решения должна выполняться жестким путем. Обратитесь к этому SO о том, как вместо этого удалить Identity из первичного ключа: Удалить идентификатор из столбца в таблице Этот сценарий отключит идентификацию на ПК:

***********************

sp_configure 'allow update', 1
go
reconfigure with override
go


update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
where id = object_id('table_name') and name = 'column_name'
go


exec sp_configure 'allow update', 0
go
reconfigure with override
go

***********************

Затем вы можете установить отношения, чтобы они обновляли ссылки на внешние ключи. Или вам нужно отключить принудительное применение отношений. Эта ссылка SO показывает, как: Как можно временно отключить ограничения внешнего ключа с помощью T -SQL?

Теперь вы можете делать свои обновления. Я написал короткий скрипт для написания всех моих обновлений SQL на основе того же имени столбца (в моем случае мне нужно было увеличить CaseID на 1000000:

select 
'update ['+c.table_name+'] SET ['+Column_Name+']=['+Column_Name+']+1000000'
from Information_Schema.Columns as c
JOIN Information_Schema.Tables as t ON t.table_Name=c.table_name and t.Table_Schema=c.table_schema and t.table_type='BASE TABLE'
where Column_Name like 'CaseID' order by Ordinal_position

Наконец, повторно включите ссылочную целостность, а затем снова включите столбец Identity на первичном ключе.

Примечание: я вижу, что некоторые люди, отвечающие на эти вопросы, спрашивают ПОЧЕМУ. В моем случае мне нужно объединить данные из второго производственного экземпляра в основную БД, чтобы я мог закрыть второй экземпляр. Мне просто нужно, чтобы все операционные данные PK / FK не конфликтовали. FK метаданных идентичны.

У меня была аналогичная проблема, мне нужно было обновить некоторые идентификаторы, что я сделал (мне нужно было увеличить их на 10k):

set identity_insert YourTable ON

INSERT INTO YourTable 
   ([ID]
   ,[something1]
   ,[something2]
   ,[something3])
SELECT 
   ([ID] + 10000)
   ,[something1]
   ,[something2]
   ,[something3]) 
FROM YourTable
WHERE something1 = 'needs updeted id' 
   AND something2 = 'some other condition'

set identity_insert YourTable OFF

DELETE FROM YourTable 
WHERE ID >= 'your old ID From' 
   AND ID <= 'Your old ID To' 

И это все. Надеюсь, вы понимаете эту логику, в моем случае было также соединение ключей PK-FK с другими таблицами, что означало, что мне пришлось обновить их, прежде чем я смогу удалить из исходных строк 'YourTable'.

Я знаю, что на это уже есть ответы, я просто хотел оставить SQL-запрос в качестве примера,

Я сделал следующее:

1. ПЕРЕМЕСТИТЬ связанные данные во временное хранилище
2. ОБНОВЛЕНИЕ значения столбца первичного ключа / идентификатора (удаление и создание ограничений)
3. RE-INSERT связанные данные с новым значением внешнего ключа

Я заключил свое решение в СОХРАНЕННУЮ ПРОЦЕДУРУ:

CREATE PROCEDURE [dbo].[UpdateCustomerLocationId]
    @oldCustomerLocationId INT,
    @newCustomerLocationId INT
AS
  /* 
    Updates CustomerLocation.CustomerLocationId @oldCustomerLocationId to @newCustomerLocationId
    
    Example:
        EXEC [dbo].[UpdateCustomerLocationId] 
            @oldCustomerLocationId = 6154874, 
            @newCustomerLocationId = 50334;
    */

BEGIN

    SET NOCOUNT ON;
    
    -- exit if @oldCustomerLocationId does not exists
    IF NOT EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @oldCustomerLocationId, ''' (@oldCustomerLocationId) does not exist in dbo.CustomerLocation');
        RETURN 1; -- 0 = success, > 0 = failure
    END

    -- exit if @newCustomerLocationId already exists
    IF EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @newCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @newCustomerLocationId, ''' (@newCustomerLocationId) already exists in dbo.CustomerLocation');
        RETURN 2; -- 0 = success, > 0 = failure
    END
    

    BEGIN TRAN;


        BEGIN -- MOVE related data into temporary storage

            IF EXISTS (SELECT * FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId) BEGIN
    
                IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL
                    DROP TABLE #CustomerLocationData;

                SELECT * INTO #CustomerLocationData FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;
                DELETE t FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;

            END

        END


        BEGIN -- UPDATE dbo.CustomerLocation

            -- DROP CONSTRAINTs
            ALTER TABLE [dbo].[CustomerLocation] DROP CONSTRAINT [UC_CustomerLocation];

            -- INSERT OLD record with new CustomerLocationId
            SET IDENTITY_INSERT dbo.CustomerLocation ON;

            INSERT INTO dbo.CustomerLocation
            (
                CustomerLocationId, CustomerId, LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, 
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, 
                Comments, LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            )
            SELECT @newCustomerLocationId AS CustomerLocationId, CustomerId,LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId,
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer,
                Comments,LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            FROM dbo.CustomerLocation 
            WHERE CustomerLocationId = @oldCustomerLocationId;

            SET IDENTITY_INSERT dbo.CustomerLocation OFF;

            -- DELETE OLD record
            DELETE cl FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId;

            -- ADD CONSTRAINTS
            ALTER TABLE [dbo].[CustomerLocation] ADD CONSTRAINT [UC_CustomerLocation] UNIQUE NONCLUSTERED ([CustomerId], [LocationId]);

        END


        BEGIN -- re-INSERT related data from temporary storage

            IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL BEGIN
                SET IDENTITY_INSERT dbo.CustomerLocationData ON;
                INSERT INTO dbo.CustomerLocationData (Guid, CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data)
                SELECT Guid, @newCustomerLocationId CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data FROM #CustomerLocationData;
                SET IDENTITY_INSERT dbo.CustomerLocationData OFF;
            END

        END


    COMMIT TRAN;

END