Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table Name.
Cannot perform SET operation for table Table Name.

To illustrate, let's say you have the following table definition:

CREATE TABLE [dbo].[IdentityInsert] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

CREATE TABLE [dbo].[IdentityInsert2] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert2] ON

Since the IDENTITY_INSERT has been set to ON for the [dbo].[IdentityInsert] table and it has not been set back to OFF, setting it to ON for another table, [dbo].[IdentityInsert2] generates this error.

Solution/Workaround:

To avoid this error, make sure that every time the IDENTITY_INSERT is set to ON, set it back to OFF after the INSERT statement.

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert] OFF

SET IDENTITY_INSERT [dbo].[IdentityInsert2] ON

INSERT INTO [dbo].[IdentityInsert2] ( [ID], [Name] )
VALUES ( 1, 'Mickey Mouse')

SET IDENTITY_INSERT [dbo].[IdentityInsert2] OFF

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint Constraint Name.
Cannot insert duplicate key in object Table Name.
The statement has been terminated.

As an example, suppose you have a table containing the different loan types that your application are accepting:

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]	VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]	      VARCHAR(50) NOT NULL
)
GO

And your table already contains the following loan types:

Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

If you try to add another loan type where the Loan Type ID already exists without knowing that it is already in the table, you will get the error.

INSERT INTO [dbo].[Loan Type] ( [Loan Type ID], [Name] )
VALUES ('HOME EQUITY', 'Home Equity Loan')

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Loan_Type'.
Cannot insert duplicate key in object 'Loan Type'.
The statement has been terminated.

Solution/Workaround:

To avoid this error, check first if a record with a given key already exists in the table and if it doesn’t exist yet, then perform the INSERT:

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = 'HOME EQUITY')
INSERT INTO [dbo].[Loan Type] ( [Loan Type ID], [Name] )
VALUES ('HOME EQUITY', 'Home Equity Loan')

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name.
The conflict occurred in database Database Name, table Table Name, column Column Name.
The statement has been terminated.

To illustrate, assuming that in your Loans System, you have two tables, a table containing the different loan types accepted by the system ([dbo].[Loan Type]) and a table containing the loan applications ([dbo].[Loan Application]). The Loan Type ID in the Loan Application table references the Loan Type ID in the Loan Type table.

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]	VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]		VARCHAR(50) NOT NULL
)
GO

CREATE TABLE [dbo].[Loan Application] (
[Loan ID]        INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Loan Type ID]   VARCHAR(20)  NOT NULL
REFERENCES [dbo].[Loan Type] ( [Loan Type ID] ),
[Borrower]       VARCHAR(100) NOT NULL
)
GO

Here’s some sample records from the 2 tables:

[dbo].[Loan Type]
Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

[dbo].[Loan Application]
Loan ID  Loan Type ID  Borrower             
-------- ------------- -------------------- 
1        HOME          Old MacDonald
2        HOME          Three Little Pigs
3        CAR           Cinderella
4        STUDENT       Peter Pan

Due to changes in business requirements, you may be asked to delete the Student Loan from the available loan types accepted by the company.

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = 'STUDENT'
GO

But since there’s an existing record in the Loan Application table that references the Student Loan loan type, you get the following error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan Appl_Loan'.
The conflict occurred in database 'TestDb', table 'Loan Application', column 'Loan Type ID'.
The statement has been terminated.

Solution/Workaround:

One way to avoid this error is to first delete all records from the other tables that reference the PRIMARY KEY.

DELETE FROM [dbo].[Loan Application]
WHERE [Loan Type ID] = ‘STUDENT’
GO

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = ‘STUDENT’
GO

But deleting records from other tables may not be acceptable because these records may still be needed.  An alternative to the physical deletion of the record is the implementation of a logical deletion of the record. This can be done by adding a new column in the table that will determine if the record is still active or not.  A bit column can serve as a status flag wherein a value of 1 means that the record is still active while a value of 0 means that the record is not used anymore.

ALTER TABLE [dbo].[Loan Type]
ADD [Status] BIT NOT NULL DEFAULT (1)
GO

UPDATE [dbo].[Loan Type]
SET [Status] = 0
WHERE [Loan Type ID] = ‘STUDENT’
GO

Loan Type ID  Name               Status
------------- ------------------ ------
CAR           Car Loan           1
HOME          Home Loan          1
HOME EQUITY   Home Equity Loan   1
PERSONAL      Personal Loan      1
STUDENT       Student Loan       0

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint Foreign Key Constraint Name.
The conflict occurred in database Database Name, table Table Name, column Column Name.
The statement has been terminated.

To illustrate, assuming that in your Loans System, you have two tables, a table containing the different loan types accepted by the system ([dbo].[Loan Type]) and a table containing the loan applications ([dbo].[Loan Application]).  The Loan Type ID in the Loan Application table references the Loan Type ID primary key in the Loan Type table.

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]	VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]		VARCHAR(50) NOT NULL
)
GO

CREATE TABLE [dbo].[Loan Application] (
[Loan ID]        INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Loan Type ID]   VARCHAR(20)  NOT NULL
REFERENCES [dbo].[Loan Type] ( [Loan Type ID] ),
[Borrower]       VARCHAR(100) NOT NULL
)
GO

Here’s some sample records from the 2 tables:

[dbo].[Loan Type]
Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

[dbo].[Loan Application]
Loan ID  Loan Type ID  Borrower             
-------- ------------- -------------------- 
1        HOME          Old MacDonald
2        HOME          Three Little Pigs
3        CAR           Cinderella
4        STUDENT       Peter Pan

Assuming that you want to differentiate between a New Car Loan and a Pre-Owned/Used Car Loan and you want to change the Loan Type ID for the current loan type from the value of ‘CAR’ to ‘NEW CAR’ using the following UPDATE statement:

UPDATE [dbo].[Loan Type]
SET [Loan Type ID] = 'NEW CAR'
WHERE [Loan Type ID] = 'CAR'

Since the foreign key constraint did not specify the action to be performed when an update on the primary key is performed, by default the action is NO ACTION.  Given this, when a change in the primary key value is performed on the Loan Type table using the UPDATE statement above, the following error is generated:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan_Appl_Loan_Type'.
The conflict occurred in database 'TestDB', table 'Loan Application', column 'Loan Type ID'.
The statement has been terminated.

Solution/Workaround:

To avoid this error from happening when the primary key value is updated on a table, make sure to specify CASCADE as the action to be performed on an update of the primary key during the creation of the table:

CREATE TABLE [dbo].[Loan Application] (
[Loan ID]        INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Loan Type ID]   VARCHAR(20)  NOT NULL
REFERENCES [dbo].[Loan Type] ( [Loan Type ID] )
ON UPDATE CASCADE,
[Borrower]       VARCHAR(100) NOT NULL
)
GO

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

To illustrate, the following SELECT statements will generate the error:

SELECT CAST('02/29/2006' AS DATETIME) -- 2006 Not a Leap Year
        SELECT CAST('06/31/2006' AS DATETIME) -- June only has 30 Days
        SELECT CAST('13/31/2006' AS DATETIME) -- There are only 12 Months
        SELECT CAST('01/01/1600' AS DATETIME) -- Year is Before 1753

Another way the error may be encountered is when the format of the date string does not conform to the format expected by SQL Server as set in the SET DATEFORMAT command.  To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY format, the following statement will generate the error:

SELECT CAST('31-01-2006' AS DATETIME)

Solution/Workaround:

To avoid this error from happening, you can check first to determine if a certain date in a string format is valid using the ISDATE function. The ISDATE function determines if a certain expression is a valid date. So if you have a table where one of the columns contains date values but the column is defined as VARCHAR data type, you can do the following query to identify the invalid dates:

SELECT * FROM [dbo].[CustomerTransactions]
        WHERE ISDATE([TranDate]) = 0
        

Once the invalid dates have been identified, you can have them fixed manually then you can use the CAST function to convert the date values into DATETIME data type:

SELECT CAST([TranDate] AS DATETIME) AS [TranDate]
        FROM [dbo].[CustomerTransactions]
        

Another way to do this without having to update the table and simply return a NULL value for the invalid dates is to use a CASE condition:

SELECT CASE ISDATE([TranDate]) WHEN 0
        THEN CAST([TranDate] AS DATETIME)
        ELSE CAST(NULL AS DATETIME) END AS [TranDate]
        FROM [dbo].[CustomerTransactions]