¿Cómo cambiar los valores de las columnas de identidad mediante programación?
Tengo una base de datos MS SQL 2005 con una tabla Test
con columna ID
. ID
es una columna de identidad.
Tengo filas en esta tabla y todas ellas tienen su valor autoincrementado ID correspondiente.
Ahora me gustaría cambiar cada ID en esta tabla de esta manera:
ID = ID + 1
Pero cuando hago esto me sale 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 la identidad establecida en esta columna, pero necesito cambie los valores también de vez en cuando. Así que mi pregunta es cómo lograr esta tarea.
13 answers
Necesitas
set identity_insert YourTable ON
Luego borre su fila y vuelva a insertarla con una identidad diferente.
Una vez que haya hecho la inserción, no olvide desactivar identity_insert
set identity_insert YourTable OFF
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-01-14 15:34:50
IDENTITY
los valores de columna son inmutables.
Sin embargo, es posible cambiar los metadatos de la tabla para eliminar la propiedad IDENTITY
, realizar 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 directa 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-06-27 12:37:36
A través de la interfaz de usuario en SQL Server 2005 manager, cambie la columna elimine la propiedad autonumber (identidad) de la columna (seleccione la tabla haciendo clic derecho sobre ella y elija "Diseño").
Luego ejecute su consulta:
UPDATE table SET Id = Id + 1
Luego vaya y agregue la propiedad autonumber a la columna.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-10 13:46:22
En primer lugar, la configuración de IDENTITY_INSERT on o off para el caso no funcionará para lo que necesita (se utiliza para insertar nuevos valores, como tapar huecos).
Haciendo la operación a través de la GUI simplemente crea una tabla temporal, copia todos los datos a través de una nueva tabla sin un campo de identidad, y cambia el nombre de la tabla.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-04-15 13:32:50
Esto se puede hacer usando una tabla temporal.
La idea
- desactivar restricciones (en caso de que su id sea referenciada por una clave foránea)
- crear una tabla temporal con el nuevo id
- suprímase el contenido de la tabla
- copie los datos de la tabla copiada a su tabla original
- habilitar restricciones previamente deshabilitadas
Consultas SQL
Digamos que su tabla test
tiene dos columnas adicionales (column2
y column3
) y que hay son 2 tablas que tienen claves foráneas que hacen referencia a 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 todo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-08-30 15:09:19
DBCC CHECKIDENT ( 'databasename.dbo.orders', RESEED, 999) puede cambiar cualquier número de columna de identidad con este comando,y también puede iniciar ese número de campo desde cada número que desee.por ejemplo, en mi comando pido comenzar desde 1000 (999 + 1) espero que sea suficiente...buena suerte
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-11 20:18:55
Si la columna no es un PK, siempre puede crear una NUEVA columna en la tabla con los números incrementados, eliminar el original y luego modificar el nuevo para que sea el antiguo.
Curioso en cuanto a por qué es posible que tenga que hacer esto... la mayoría que he tenido que futz con columnas de identidad fue para rellenar números y acabo de terminar usando DBCC CHECKIDENT (tablename,RESEED,newnextnumber)
¡Buena suerte!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-04-15 13:01:03
La modificación de identidad puede fallar dependiendo de una serie de factores, principalmente en torno a los objetos/relaciones vinculados a la columna id. Parece que el diseño de la base de datos es tan problemático aquí como los id rara vez 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 de id ficticia que no sea la clave principal/número automático que pueda administrar usted mismo y generar a partir de los valores actuales. Alternativamente, la idea de Chrisotphers anterior sería mi otra sugerencia si tiene problemas para permitir la inserción de identidad.
Buena suerte
PS no está fallando porque el orden secuencial en el que se está ejecutando está tratando de actualizar un valor en la lista a un elemento que ya existe en la lista de ids? agarrándose a una pajita, tal vez sumar el número de filas + 1, luego si eso funciona restar el número de filas: - S
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-04-15 13:10:56
Si necesita cambiar los ID de vez en cuando, probablemente sea mejor no usar una columna de identidad. En el pasado hemos implementado campos autonumber manualmente usando una tabla 'Counters' que rastrea el siguiente ID para cada tabla. IIRC hicimos esto porque las columnas de identidad estaban causando la corrupción de la base de datos en SQL2000, pero ser capaz de cambiar los IDs fue ocasionalmente útil para las pruebas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-07-27 10:18:12
Puede insertar filas nuevas con valores modificados y luego eliminar filas antiguas. El siguiente ejemplo cambia el ID para que sea el mismo que la clave foránea 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-03-26 04:57:30
Primero guarde todos los ID y alterarlos programáticamente a los valores que no desea, luego elimínelos de la base de datos y luego insértelos nuevamente 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 insertos a granel:
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 del archivo.csv:
2;
3;
4;
5;
6;
Si no establece identity_insert
en off, obtendrá el siguiente error:
No se puede insertar un valor explícito para la columna de identidad en la tabla 'Test' cuando IDENTITY_INSERT está establecido en OFF.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-08 13:34:46
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 mal y tuve que eliminar de nuevo. Una vez que eliminé las filas, mi columna de identidad cambió . Estaba tratando de encontrar una manera de actualizar la columna que se insertó , pero-no hubo suerte. Entonces, mientras buscaba en Google encontró un enlace ..
- Eliminado las columnas que se insertaron erróneamente
- Use force inserte usando la identidad on / off (explicado más adelante)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-09-25 04:42:25
Muy buena pregunta, primero necesitamos en la IDENTITY_INSERT para la tabla específica, después de eso ejecute la consulta insert (Debe especificar el nombre de la columna).
Nota: Después de editar la columna identidad, no olvides desactivar el IDENTITY_INSERT. Si no lo ha hecho, no podrá editar la columna identidad de 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-07-27 10:01:32