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
Is There a Performance Difference Between Cte , Sub-Query, Temporary Table or Table Variable
Postgresql Sequence Based on Another Column
How to Query a Comma Separated Column for a Specific Value
Calculate Business Hours Between Two Dates
SQL Syntax Term for 'Where (Col1, Col2) < (Val1, Val2)'
SQL Query to Select Distinct Row with Minimum Value
How to Convert a SQL Server 2008 Datetimeoffset to a Datetime
Conversion of a Varchar Data Type to a Datetime Data Type Resulted in an Out-Of-Range Value
How to Show All Privileges from a User in Oracle
Get Next Sequence Value from Database Using Hibernate
Sql:In Clause in Stored Procedure:How to Pass Values
Postgres Dynamic Query Function
Fastest Way to Determine If Record Exists
How to Perform a Group by on an Aliased Column in SQL Server
Database Eav Pros/Cons and Alternatives