miércoles, 21 de noviembre de 2012

Errors inserting identity on table

Here we've different errors that can occurs trying to insert some rows into table that has Identity field.

I want to insert all data from Table Banca_Movimientos_TEMP to table Banca_Movimientos3

Sentence 1:


insert into dbo.Banca_Movimientos3 
select * from dbo.Banca_Movimientos_TEMP


On executing this, I get the error:


Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.Banca_Movimientos3' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This is because it's necessary to specify all fields in insert into statement.

Let's do it.


Sentence 2:

insert into dbo.Banca_Movimientos3([IdMovimiento] ,[Concepto] ,[Fecha_Operacion] ,[Fecha_Valor] ,[Descripcion] ,[Importe] ,[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

Well, now another error occurs.


Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Banca_Movimientos3' when IDENTITY_INSERT is set to OFF.

It's needed to set IDENTITY_INSERT ON because field IdMovimiento is IDENTITY.


Sentence 3:

SET IDENTITY_INSERT Banca_Movimientos3 ON;
GO

insert into dbo.Banca_Movimientos3 ([IdMovimiento],[Concepto],[Fecha_Operacion],[Fecha_Valor],[Descripcion],[Importe],[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

SET IDENTITY_INSERT Banca_Movimientos3 OFF;
GO

(1385 row(s) affected)

Now, the insert statement is OK.