How to Avoid Duplicate Values for Insert in SQL

How to avoid Duplicate values for INSERT in SQL?

Before inserting check if there is a record with the same values:

if not exists (select * from Delegates d where d.FromYr = @FromYr and d.MemNo = @MemNo)
INSERT INTO Delegates ([MemNo],[FromYr],[ToYr]) values(@MemNo, @FromYr,@ToYr)

Avoid duplicates in INSERT INTO SELECT query in SQL Server

Using NOT EXISTS:

INSERT INTO TABLE_2
(id, name)
SELECT t1.id,
t1.name
FROM TABLE_1 t1
WHERE NOT EXISTS(SELECT id
FROM TABLE_2 t2
WHERE t2.id = t1.id)

Using NOT IN:

INSERT INTO TABLE_2
(id, name)
SELECT t1.id,
t1.name
FROM TABLE_1 t1
WHERE t1.id NOT IN (SELECT id
FROM TABLE_2)

Using LEFT JOIN/IS NULL:

INSERT INTO TABLE_2
(id, name)
SELECT t1.id,
t1.name
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL

Of the three options, the LEFT JOIN/IS NULL is less efficient. See this link for more details.

Avoid duplicates in INSERT INTO in the same table

If the version of SQLite you use is 3.24.0+ and there is a unique constraint for the column city, you can use upsert which gives you an option to do NOTHING or UPDATE the table if a unique constraint violation occurs.

In this case:

String sql = 
"INSERT INTO weather VALUES(?,?,?,?,?,?,?) " +
"ON CONFLICT DO NOTHING";

if you try to insert a row with an existing city, the statement will fail without an error.

But if the new row contains up to date data for the other columns and you want the row updated, you can do this:

String sql = 
"INSERT INTO weather VALUES(?,?,?,?,?,?,?) " +
"ON CONFLICT(city) DO UPDATE SET "
"temp = excluded.temp, " +
"feels_like = excluded.feels_like, " +
"temp_min = excluded.temp_min, " +
"temp_max = excluded.temp_max, " +
"pressure = excluded.pressure, " +
"humidity = excluded.humidity";

and the other 6 columns will be overwritten by the new values you supplied.

If there isn't a unique constraint defined for city and you don't want to or can't define one, then you can avoid inserting the same city twice with NOT EXISTS like this:

String sql = 
"INSERT INTO weather SELECT ?,?,?,?,?,?,? " +
"WHERE NOT EXISTS (SELECT 1 FROM weather WHERE city = ?);

In this case you will have to pass in your Java code as an additional 8th parameter the value of the city again.

How to avoid duplicate record while inserting data using user defined table type in SQL Server

Please find the changes done to your script to avoid inserting duplicate record. So i considered two columns data should be unique to avoid duplication for user understanding purpose

 CREATE PROCEDURE [dbo].[proc_SaveAccessRequest] 
(
@TmpAR TmpAccessRequest READONLY,
@IsUAMSRequest bit,
@RequestID int OUTPUT
)
AS
BEGIN

Insert into tblRequests
(
RequesterID
,RequestType
,NextApprover
,RequestStatus
,Delegation
,CreatedOn
,CreatedBy
,[Description]
,IsSepecialRequest
,DelegationDetailID
,IsActive
,IsDeleted
,ModifiedOn
)
SELECT
RequesterID
,RequestType
,NextApprover
,RequestStatus
,Delegation
,CreatedOn
,CreatedBy
,Description
,IsSepecialRequest
,DelegationDetailID
,IsActive
,IsDeleted
,ModifiedOn
FROM @TmpAR
WHERE NOT EXISTS ( SELECT 1
FROM tblRequests i
INNER JOIN @TmpAR o
ON i.RequesterID = o.RequesterID
AND i.RequestType = o.RequestType
AND i.NextApprover = o.NextApprover)

SELECT @RequestID = SCOPE_IDENTITY()

SELECT @RequestID
END

Avoid duplicates on INSERT INTO SELECT query in SQL Server

The problem is that select distinct is not sufficient. You still have duplicates in the underlying table, but with different names or descriptions.

I view this as a problem. But, you can work around it by selecting one arbitrary row per cust_code, using row_number():

insert into dbo.Entities (EntityId, [Name], [Description], [Type], Source)
select CUST_CODE, NAME, FULLDESCRIPTION, 'Agency' AS [Type], 'SunDbAgencies' AS Source
from (select a.*,
row_number() over (partitoin by cust_code order by cust_code) as seqnum
from dbo.VW_SUNDB_AGENCIES a
) a
where seqnum = 1 and
not exists (select 1 from dbo.Entities E where A.CUST_CODE = E.EntityId);

SQL Prevent Duplicate INSERT

I ended up writing another if statement to check if a unique value existed from incoming and the existing db value existed and leaving it blank to prevent it from importing duplicates. I also wrote a separate file to update where values differentiate between what I am receiving as (new) and what is in the database (old) which actually worked out great for my application.

Here is my answer for anyone else that runs into this issue :)

$prep_stmt = "SELECT * FROM table WHERE column_keys=?";
$stmt = $mysqli->prepare($prep_stmt);

if ($stmt) {
$stmt->bind_param('s',$varvalues);
$stmt->execute();
$stmt->store_result();

if ($stmt->num_rows == 1) {

if ($insert_stmt = $mysqli->prepare("")) {
$insert_stmt->bind_param('');

if (! $insert_stmt->execute()) {
echo 'shits broke'; }
}
}
else { if ($insert_stmt = $mysqli->prepare("
INSERT INTO table (column_keys)
VALUES (?)")) // you will need a ? per column seperate by a , (?,?,?...?)

{ $insert_stmt->bind_param('s',
$varvalues
); // you will also need to bind a 's' (string) 'i' for num, etc per $var value.

if (! $insert_stmt->execute()) { echo 'shits broke';} //lol
}
}
}

Also a simple error reporting trick I stumbled upon that helped me clean up a few things I overlooked. Just place it at the top of the file, or above you want to debug ;)

error_reporting(E_ALL);

Restrict duplicate records to insert into table at same time

You may try adding a unique constraint to the dat column:

ALTER TABLE time ADD CONSTRAINT cnstr_dat UNIQUE (dat);

This would prevent duplicate records from being inserted.



Related Topics



Leave a reply



Submit