sql-server - sp3 - sql server 2008
¿Cómo cambiar los valores de la columna de identidad de forma programática? (13)
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.
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.
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
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.
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.
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
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
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
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.
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
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!
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.
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 ...
- Eliminado las columnas que se insertaron incorrectamente
- Use inserción de fuerza usando identidad activada / desactivada (se explica a continuación)
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