sql-server - sp3 - sql server 2008

¿Cómo cambiar los valores de la columna de identidad de forma programática?

Question

Tengo una base de datos MS SQL 2005 con una Test tabla con ID columna. ID es una columna de identidad.

Tengo filas en esta tabla y todas tienen su correspondiente valor autoincrementado.

Ahora me gustaría cambiar cada identificación en esta tabla como esta:

ID = ID + 1

Pero cuando hago esto obtengo un error:

Cannot update identity column ''ID''.

He intentado esto:

ALTER TABLE Test NOCHECK CONSTRAINT ALL 
set identity_insert ID ON

Pero esto no resuelve el problema.

Necesito tener una identidad establecida en esta columna, pero también necesito cambiar los valores de vez en cuando. Entonces mi pregunta es cómo lograr esta tarea.

Answer 1

A través de la interfaz de usuario en el administrador de SQL Server 2005, cambie la columna elimine la propiedad de autonumeración (identidad) de la columna (seleccione la tabla haciendo clic derecho sobre ella y elija "Diseño").

Luego ejecuta tu consulta:

UPDATE table SET Id = Id + 1

Luego vaya y agregue la propiedad autonumber a la columna.

Answer 2

DBCC CHECKIDENT (''databasename.dbo.orders'', RESEED, 999) puede cambiar cualquier número de columna de identidad con este comando, y también puede comenzar ese número de campo de cada número que desee. Por ejemplo, en mi comando pido que comience desde 1000 (999 + 1) esperan que sea suficiente ... buena suerte

Answer 3

En primer lugar, la configuración de IDENTITY_INSERT activada o desactivada para ese asunto no funcionará para lo que necesita (se usa para insertar nuevos valores, como tapar huecos).

Hacer la operación a través de la GUI solo crea una tabla temporal, copia todos los datos en una nueva tabla sin un campo de identidad y cambia el nombre de la tabla.

Answer 4

Esto se puede hacer usando una tabla temporal.

La idea

• deshabilite las restricciones (en caso de que su identificador sea referenciado por una clave externa)
• crea una tabla temporal con la nueva identificación
• eliminar el contenido de la tabla
• copiar datos de la tabla copiada a su tabla original
• habilitar restricciones deshabilitadas previsiblemente

Consultas SQL

Digamos que su tabla de test tiene dos columnas adicionales ( column2 y column3 ) y que hay 2 tablas con claves externas que hacen referencia a la test llamadas foreign_table1 y foreign_table2 (porque los problemas de la vida real nunca son simples).

alter table test nocheck constraint all;
alter table foreign_table1 nocheck constraint all;
alter table foreign_table2 nocheck constraint all;
set identity_insert test on;

select id + 1 as id, column2, column3 into test_copy from test v;
delete from test;
insert into test(id, column2, column3)
select id, column2, column3 from test_copy

alter table test check constraint all;
alter table foreign_table1 check constraint all;
alter table foreign_table2 check constraint all;
set identity_insert test off;
drop table test_copy;

Eso es.

Answer 5

La modificación de identidad puede fallar dependiendo de una serie de factores, principalmente en torno a los objetos / relaciones vinculados a la columna de id. Parece que el diseño de bases de datos es un problema aquí, ya que los identificadores rara vez o nunca deberían cambiar (estoy seguro de que tiene sus razones y está implementando los cambios). Si realmente necesita cambiar los id de vez en cuando, le sugiero que cree una nueva columna ficticia que no sea la clave principal / autonumber que puede administrar usted mismo y generar a partir de los valores actuales. Alternativamente, la idea de Chrisotphers anterior sería mi otra sugerencia si tienes problemas para permitir el inserto de identidad.

Buena suerte

PD, ¿no está fallando porque el orden secuencial en el que se está ejecutando trata de actualizar un valor en la lista a un elemento que ya existe en la lista de identificadores? agarrándose a las pajitas, quizás agregue el número de filas + 1, entonces si eso funciona, reste la cantidad de filas: -S

Answer 6

Muy buena pregunta, primero necesitamos en IDENTITY_INSERT para la tabla específica, después de eso ejecutar la consulta de inserción (debe especificar el nombre de la columna).

Nota: Después de editar la columna de identidad, no olvide desactivar IDENTITY_INSERT. Si no lo ha hecho, no podrá Editar la columna de identidad para ninguna otra tabla.

SET IDENTITY_INSERT Emp_tb_gb_Menu ON
     INSERT Emp_tb_gb_Menu(MenuID) VALUES (68)
SET IDENTITY_INSERT Emp_tb_gb_Menu OFF

http://allinworld99.blogspot.com/2016/07/how-to-edit-identity-field-in-sql.html

Answer 7

Necesitas

set identity_insert YourTable ON

Luego borre su fila y vuelva a insertarla con una identidad diferente.

Una vez que hayas hecho la inserción, no olvides desactivar identity_insert

set identity_insert YourTable OFF

Answer 8

Primero guarde todas las identificaciones y modifíquelas programáticamente según los valores que desee, luego elimínelas de la base de datos y luego vuelva a insertarlas usando algo similar:

use [Name.Database]
go
set identity_insert [Test] ON
insert into [dbo].[Test]
           ([Id])
     VALUES
           (2)
set identity_insert [Test] OFF

Para uso de inserciones en bloque:

use [Name.Database]
go
set identity_insert [Test] ON
BULK INSERT [Test]
FROM ''C:/Users/Oscar/file.csv''
WITH (FIELDTERMINATOR = '';'',
      ROWTERMINATOR = ''/n'',
      KEEPIDENTITY)
set identity_insert [Test] OFF

Datos de muestra de file.csv:

2;
3;
4;
5;
6;

Si no configura identity_insert en off obtendrá el siguiente error:

No se puede insertar un valor explícito para la columna de identidad en la tabla ''Prueba'' cuando IDENTITY_INSERT está establecido en OFF.

Answer 9

Puede insertar nuevas filas con valores modificados y luego eliminar las filas antiguas. El siguiente ejemplo cambia ID para que sea igual a la clave externa PersonId

SET IDENTITY_INSERT [PersonApiLogin] ON

INSERT INTO [PersonApiLogin](
       [Id]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess])
SELECT [PersonId]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess]
FROM [db304].[dbo].[PersonApiLogin]
GO

DELETE FROM [PersonApiLogin]
WHERE [PersonId] <> ID
GO
SET IDENTITY_INSERT [PersonApiLogin] OFF
GO

Answer 10

Si la columna no es un PK, siempre puede crear una NUEVA columna en la tabla con los números incrementados, soltar el original y luego alterar el nuevo para que sea el anterior.

Tengo curiosidad por saber por qué es posible que deba hacer esto ... lo que más he tenido que hacer con las columnas de Identidad fue rellenar los números y terminé usando DBCC CHECKIDENT (nombre de la tabla, RESEED, newnextnumber)

¡buena suerte!

Answer 11

Si necesita cambiar los ID ocasionalmente, probablemente sea mejor no usar una columna de identidad. En el pasado, implementamos campos de autonumeración manualmente usando una tabla de "Contadores" que rastrea el siguiente ID para cada tabla. El IIRC lo hicimos porque las columnas de identidad estaban causando daños en la base de datos en SQL2000, pero el hecho de poder cambiar los identificadores ocasionalmente era útil para las pruebas.

Answer 12

Vi un buen artículo que me ayudó en el último momento. Estaba tratando de insertar algunas filas en una tabla que tenía una columna de identidad, pero lo hice de manera incorrecta y tengo que borrar de nuevo. Una vez que eliminé las filas, mi columna de identidad cambió. Estaba intentando encontrar una forma de actualizar la columna que se insertó, pero no tuve suerte. Entonces, mientras buscaba en google encontró un enlace ...

1. Eliminado las columnas que se insertaron incorrectamente
2. Use inserción de fuerza usando identidad activada / desactivada (se explica a continuación)

http://beyondrelational.com/modules/2/blogs/28/posts/10337/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do-i-update-the-value-of-an.aspx

Answer 13

IDENTITY valores de la columna de IDENTITY son inmutables.

Sin embargo, es posible cambiar los metadatos de la tabla para eliminar la propiedad IDENTITY , hacer la actualización y luego volver a cambiar.

Asumiendo la siguiente estructura

CREATE TABLE Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test 
OUTPUT INSERTED.*
SELECT ''Foo'' UNION ALL
SELECT ''Bar'' UNION ALL
SELECT ''Baz''

Entonces puedes hacer

/*Define table with same structure but no IDENTITY*/
CREATE TABLE Temp
(
ID INT PRIMARY KEY,
X VARCHAR(10)
)

/*Switch table metadata to new structure*/
ALTER TABLE Test SWITCH TO Temp;

/*Do the update*/
UPDATE Temp SET ID = ID + 1;

/*Switch table metadata back*/
ALTER TABLE Temp SWITCH TO Test;

/*ID values have been updated*/
SELECT *
FROM Test

/*Safety check in case error in preceding step*/
IF NOT EXISTS(SELECT * FROM Temp)
    DROP TABLE Temp /*Drop obsolete table*/

En SQL Server 2012 es posible tener una columna de incremento automático que también se puede actualizar de manera más sencilla con SEQUENCES

CREATE SEQUENCE Seq
    AS INT
    START WITH 1
    INCREMENT BY 1

CREATE TABLE Test2
(
ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test2(X)
SELECT ''Foo'' UNION ALL
SELECT ''Bar'' UNION ALL
SELECT ''Baz''

UPDATE Test2 SET ID+=1