Very Common SQL Server Errors Part 1

In this article I would like to list out the Top 5 errors that we get when working with DML commands.

Error: 1
Msg 109, Level 15, State 1, Line 1
There are more 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.

Cause:
When you have Column names in INSERT clause and you didn’t provide all the values to the columns, which are specified in INSERT clause.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp(Emp_ID,ENAME,SALARY) VALUES(1,'vijay')

If you look at the INSERT statement I didn’t provide values to all the columns in INSERT statement.

Solution:
Make sure you provide values to all the columns in the INSERT statement.

Error: 2
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Cause:
You will get this error, when you do not provide values to all the columns in a table.


How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay')

If you look at the INSERT statement I didn’t provide values to the all the columns in EMP table.

Solution:
Make sure you provide values to all the columns in the table definition.


Error: 3
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Emp'. Cannot insert duplicate key in object 'dbo.Emp'.
The statement has been terminated.

Cause:
You will get this error, when you try to insert duplicate records into the table.


How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL primary key,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay',12300)
insert into Emp VALUES(1,'kadiyala',1300)

The first insert statement gets inserted into the table but second one will fail because we have primary Key constraint on Emp_ID column.

Solution:
Make sure you don’t provide duplicate values. Try to use IDENTITY property or have constraint at the application level.

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

Cause:
You will get this error, when you try to insert values into Identity column.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT IDENTITY NOT NULL ,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay',12300)

You can’t insert value into Identity column in straight pass.

Solution:
If you want to insert the data into Identity column then you need to turn IDENTITY_INSERT ON.

Error: 5
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Cause:
You will get this error, when the Value length in INSERT statement exceeds the length defined at the time of Table creation.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT IDENTITY NOT NULL ,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES('vijay',12300)
insert into Emp VALUES('Kadiyala',12300)

The 2nd INSERT will fail because length of the ENAME column is declared as 5 Characters but the supplied value length is 8 Characters.

Solution:
Either you increase length of the column or shorten the length of the value.