IDENTITY_INSERT error in SQL

Question

IDENTITY_INSERT error in SQL

Navigation

#1 by (1 votes)

0

My problem is that I try to make a simple insert in my Database, but at the moment of executing the sentence it marks me the following error:

An explicit value for the identity column in table 'tNationalization' can only be specified when a column list is used and IDENTITY_INSERT is ON.

In my insert I have all the fields with a respective value.

Insert Code

insert into tNacionalizacion values(140545,'2016-01-21',38,12,0,0,0,0,366,22,4741,'Ninguna',0,'MB014642','FGHFH5465','2015-01-08',12917,8,38,1,123384,'Descripcion',9,1,11202,75,75,6,1,56,68,0.000,'TL',0,0,NULL,2,'2017-03-29 13:27:23.650','2017-03-29 13:47:25.097',5479,'192.168.1.87',141300,4);

Update

Modify the code of my insert in the following way:

insert into tNacionalizacion(idPedimento,fecha,idCveDocEntrada,idTipoCambio,seguros,fletes,embalajes,otros,valorcomercial,valoraduana,observación,dta,prevalidacion,factura,cove,fechafactura,idproveedor,incoterm,idMoneda,factorMoneda,numParte,descripion,idTipoBien,secuencia,idFraccion,idPaisVendedor,idPaisOrigen,idUnidadComercial,cantidad,precioUnitario,idTasa,preferencia,idForpago,descargado,pedimentorectificado,idLayout,fechaIngreso,fechaActualizacion,idUsuario,ipActualizacion,descargo,idEmpresa) values(140545,'2016-01-21',38,12,0,0,0,0,366,22,4741,'Ninguna',0,'MB014642','FGHFH5465','2015-01-08',12917,8,38,1,123384,'Descripcion',9,1,11202,75,75,6,1,56,68,0.000,'TL',0,0,NULL,2,'2017-03-29 13:27:23.650','2017-03-29 13:47:25.097',5479,'192.168.1.87',141300,4);

and the previous error was removed and a new one was put:

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

But as far as I'm concerned the fields do coincide.

Structure of my table

CREATE TABLE [dbo].[tNacionalizacion](
    [idNacionalizacion] [int] IDENTITY(1,1) NOT NULL,
    [idPedimento] [int] NOT NULL,
    [fecha] [date] NOT NULL,
    [idCveDocEntrada] [int] NOT NULL,
    [idTipoCambio] [int] NOT NULL,
    [seguros] [float] NULL,
    [fletes] [float] NULL,
    [embalajes] [float] NULL,
    [otros] [float] NULL,
    [valorComercial] [float] NOT NULL,
    [valorAduana] [float] NOT NULL,
    [observación] [varchar](max) NULL,
    [dta] [float] NULL,
    [prevalidacion] [float] NOT NULL,
    [factura] [varchar](50) NOT NULL,
    [cove] [varchar](50) NULL,
    [fechaFactura] [date] NOT NULL,
    [idProveedor] [int] NOT NULL,
    [incoterm] [varchar](3) NOT NULL,
    [idMoneda] [int] NOT NULL,
    [factorMoneda] [float] NULL,
    [numParte] [varchar](500) NOT NULL,
    [descripion] [varchar](max) NULL,
    [idTipoBien] [int] NOT NULL,
    [secuencia] [int] NOT NULL,
    [idFraccion] [int] NOT NULL,
    [idPaisVendedor] [int] NOT NULL,
    [idPaisOrigen] [int] NOT NULL,
    [idUnidadComercial] [int] NULL,
    [cantidad] [float] NOT NULL,
    [precioUnitario] [float] NOT NULL,
    [idTasa] [decimal](5, 3) NULL,
    [preferencia] [char](2) NULL,
    [idForPago] [int] NULL,
    [descargado] [int] NULL,
    [pedimentoRectificado] [varchar](20) NULL,
    [idLayout] [int] NULL,
    [fechaIngreso] [datetime] NOT NULL,
    [fechaActualizacion] [datetime] NULL,
    [idUsuario] [int] NOT NULL,
    [ipActualizacion] [varchar](60) NULL,
    [descargo] [varchar](30) NULL,
    [idEmpresa] [int] NULL,
 CONSTRAINT [PK__tNaciona__8EC33AF3797309D9] PRIMARY KEY CLUSTERED 
(
    [idNacionalizacion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

sql sql-server insert

asked by David 30.03.2017 в 00:33

source

1 answer

Check correct UPDATE in MySql with PHP-PDO Error in PHP when starting session

score 1 · Accepted Answer

As well @ Davlio says in the comments, the number of columns defined in INSERT do not match the number of values to insert. You must tie a column to a value.

Regarding the first error, it is because you have a column defined as INDENTITY , this means that the values that are saved there will be incremental automatically and you do not need to define them in your INSERT sentence, the database manager will do this work for you.

Assuming the column idPedimento is defined as IDENTITY in the following way:

CREATE TABLE [dbo].[tNacionalizacion ](
    [idPedimento] [int] IDENTITY(1,1) NOT NULL,
    [fecha] [datetime] NULL,
    [idCveDocEntrada] [int] NULL,
    --Aquí van todos los demás campos
CONSTRAINT [PK_tNacionalizacion] PRIMARY KEY CLUSTERED 
(
    [idPedimento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

To perform in INSERT , that column should not be specified:

INSERT INTO tNacionalizacion
(fecha, idCveDocEntrada, ...resto de las columnas)
VALUES 
('2016-01-21', 38, ...resto de las columnas)