Generally, we need to prevent duplicate insert while adding rows to SQL table. In This tutorial will discuss the different ways to prevent the occurrence of duplicate records in a table. Let's prepare some test data to understand it with an example
To prevent inserting duplicate records with the same EnrollmentNo values in the Student table, add a PRIMARY KEY to its definition. When you add a PRIMARY KEY on a column you should keep in your mind that column should be defined as NOT NULL because a PRIMARY KEY does not allow NULL values. Follow the given steps to add a PRIMARY KEY on existing column
(I). First, you have to delete all duplicate records from the student table if any.
(II). Use the given query to make the EnrollmentNo column as NOT NULL
Click here to know how to add a UNIQUE
KEY on a table.
We are getting this error message because the SELECT STATEMENT returning some same EnrollmentNo('BBA001', 'BBA002') which are already present in student table.
CREATE TABLE Student
(
EnrollmentNo VARCHAR(10),
Name VARCHAR(40),
Mobile VARCHAR(15)
)
GO
INSERT INTO Student(EnrollmentNo,Name,Mobile)
SELECT 'BBA001','Ram','46464654' UNION
SELECT 'BBA002','Shyam','46464654' UNION
SELECT 'BBA003','Kamal','46464654'
GO
CREATE TABLE StudentBackup
(
EnrollmentNo VARCHAR(10),
Name VARCHAR(40),
Mobile VARCHAR(15)
)
GO
INSERT INTO StudentBackup(EnrollmentNo,Name,Mobile)
SELECT 'BBA001','Ram','46464654' UNION
SELECT 'BBA002','Shyam','46464654' UNION
SELECT 'BBA004','Hari','897964665' UNION
SELECT 'BBA005','Kapil','97812652'
Stop inserting duplicate records in the student table using PRIMARY KEY or UNIQUE KEY.
You can use a PRIMARY KEY or a UNIQUE KEY on a table with the appropriate fields to stop duplicate records. As we didn't define any PRIMARY KEY or UNIQUE KEY on the student table so it can have duplicate records.To prevent inserting duplicate records with the same EnrollmentNo values in the Student table, add a PRIMARY KEY to its definition. When you add a PRIMARY KEY on a column you should keep in your mind that column should be defined as NOT NULL because a PRIMARY KEY does not allow NULL values. Follow the given steps to add a PRIMARY KEY on existing column
(I). First, you have to delete all duplicate records from the student table if any.
(II). Use the given query to make the EnrollmentNo column as NOT NULL
ALTER TABLE Student
ALTER COLUMN EnrollmentNo VARCHAR(10) NOT NULL
(III). Use the given query to add a PRIMARY KEY
ALTER TABLE Student
ADD CONSTRAINT PK_EnrollmentNo_Student PRIMARY KEY (EnrollmentNo)
Now if you try to insert duplicate EnrollmentNo in the Student table, it will prevent you from inserting and throw an error message as given below
Stop inserting duplicate records using "NOT EXISTS" or "NOT IN" or "LEFT JOIN/IS NULL"
Let's try to do a bulk insert into Student table from StudentBackup table using given queryINSERT INTO Student(EnrollmentNo,Name,Mobile)
SELECT EnrollmentNo,Name,Mobile FROM StudentBackup
We will get an error message as given below
(a) Use NOT EXISTS to remove the EnrollmentNo('BBA001','BBA002') which are already present in student table.
INSERT INTO Student(EnrollmentNo,Name,Mobile)
SELECT EnrollmentNo,Name,Mobile FROM StudentBackup sb
WHERE NOT EXISTS(SELECT s.EnrollmentNo
FROM Student s
WHERE s.EnrollmentNo=sb.EnrollmentNo)
(b) Use NOT IN to remove the EnrollmentNo('BBA001','BBA002') which are already present in student table.
INSERT INTO Student(EnrollmentNo,Name,Mobile)
SELECT EnrollmentNo,Name,Mobile FROM StudentBackup sb
WHERE sb.EnrollmentNo NOT IN(SELECT s.EnrollmentNo
FROM Student s
)
(c) Use LEFT JOIN/IS NULL to remove the EnrollmentNo('BBA001','BBA002') which are already present in student table.
INSERT INTO Student(EnrollmentNo,Name,Mobile)
SELECT sb.EnrollmentNo,sb.name,sb.Mobile
FROM StudentBackup sb
LEFT JOIN Student s ON s.EnrollmentNo=sb.EnrollmentNo
WHERE s.EnrollmentNo IS NULL
0 Comments