Check for Duplicates Before Inserting

How can I check for duplicates before inserting into a table when inserting by select

INSERT INTO table1 
SELECT t2.col1, 
       t2.col2 
FROM   table2 t2 
       LEFT JOIN table1 t1 
         ON t2.col1 = t1.col1 
            AND t2.col2 = t1.col2 
WHERE  t1.col1 IS NULL 

Alternative using except

INSERT INTO @table2 
SELECT col1, 
       col2 
FROM   table1 
EXCEPT 
SELECT t1.col1, 
       t1.col2 
FROM   table1 t1 
       INNER JOIN table2 t2 
         ON t1.col1 = t2.col1 
            AND t1.col2 = t2.col2 

Alternative using Not Exists

INSERT INTO table2 
SELECT col1,col2 
FROM table1 t1
WHERE
NOT EXISTS( SELECT 1
    FROM table2 t2
    WHERE t1.col1 = t2.col1
          AND t1.col2 = t2.col2)

How to check for duplicates before inserting into database?

You let the database do the work! Simply define a unique constraint or index on the table:

alter table favorites add constraint unq_favorites_useremail_imageid
    unique (useremail, imageid);

With this constraint in place, the database will return an error if you attempt to insert a duplicate.

If you want to avoid the error, you can use on duplicate key update:

insert into favorites (UserEmail, ImageId)
    values (?, ?)
    on duplicate key update ImageId = values(ImageId);

Some notes about this:

• The ? is a parameter placeholder. Learn to use parameters rather than munging values in query strings.
• This does not use set. That is a MySQL extension. There is no advantage in this case; you might as well use the standard syntax.
• The on duplicate key is a no-operation, but it prevents the code from returning an error when there is a duplicate.

Check for duplicates before inserting

You want to do the following:

$dupesql = "SELECT * FROM table where (name = '$name' AND description = '$description' AND manufacturer = '$manufacturer' AND city ='$city' AND price = '$price' AND enddate = '$end_date')";

$duperaw = mysql_query($dupesql);

if (mysql_num_rows($duperaw) > 0) {
  //your code ...
}

See Here for more information.

Check for duplicates before adding to the database

You could use the [Remote] validation to check whether the BrandName is exist or not, then show the validation message.

Refer the following code:

public class Brand
{
    [Key]
    public int BrandID { get; set; }

    [Required(ErrorMessage = "Enter Brandname")]
    [Display(Name = "Brandname")]
    [StringLength(30)]
    [Remote("IsAlreadyExist", "Home", HttpMethod = "POST", ErrorMessage = "BrandName already exists in database.")]
    public string BrandName { get; set; }
}

Controller:

    public IActionResult CreateBrand()
    {
        return View();
    }
    [HttpPost]
    public async Task<IActionResult> CreateBrand(Brand brand)
    {
        //insert the brand to the database
        if (ModelState.IsValid)
        {
            _context.Add(brand);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }
        return View(brand);
    }
    [HttpPost]
    public async Task<IActionResult> IsAlreadyExist(string brandname)
    {
        //check whether the brandname is exists in the database.
        if (_context.Brands.Any(x => x.BrandName == brandName))
        {
            return Json(false); //the brand name exists in the database.
        }
        return Json(true);
    }

View Page:

@model WebApplication6.Models.Brand

@{
    ViewData["Title"] = "CreateBrand";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<div class="row">
    <div class="col-md-4">
        <form asp-action="CreateBrand">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="BrandName" class="control-label"></label>
                <input asp-for="BrandName" class="form-control" />
                <span asp-validation-for="BrandName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-primary" />
            </div>
        </form>
    </div>
</div>
 
@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

The result as below:

More detail information, see [Remote] attribute.

PostgreSQL, check for duplicate before insert

Firstly - I believe that if you want to have a unique field in your table - then it is the easiest to mark it as such:

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS

If an attempted insert on such a field should not raise an error, then there is a the ON CONFLICT keyword:

https://www.postgresql.org/docs/current/sql-insert.html

Especially: ON CONFLICT DO NOTHING

Check duplicate entry before inserting into SQL Server

This post explains a solution to the problem.

Essentially you can check the existence of a particular row first using:

IF NOT EXIST( SELECT 1 FROM TutorTable WHERE Tid = @tid)
BEGIN
    <your insert statement...>
END

(This is also using a parameter instead of directly values as suggested by rene)

You can then check the return value from ExecuteNonQuery to see how many rows were actually inserted.

Insert statement that checks for duplicate before insert

INSERT INTO requests ('user_id','subject','text','time') 
VALUES (56,'test','test 1234',6516516)
ON DUPLICATE KEY UPDATE time = VALUES(time), user_id = VALUES(user_id)

Have the relevant columns set to index UNIQUE.

This will insert a row, but if subject or text (or both) already exist, you instead update the existing row with given time and user_id

How to check database for duplicates before inserting?

Let the database do the work for you. Define a unique index/constraint specifying that those three values are unique in the table:

create unique index unq_reservation_3 on reservation(plateNo, driverID, resDate);

If you attempt to insert a duplicate -- or do an update that results in a duplicate -- then the database will return an error. You simply need to catch the error.

How to check for duplicate records before inserting them to DB(2 Primary Keys)

Firstly, its not really clear whats in this table or your data types. Lets assume your data types are TagNumber: int and Date: datetime.

Next your problem is probably with the date field.

DateTime dt1 = DateTime.ParseExact(smdt1, format1, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal);

Will parse the date as you would expect. However this will also return the time. So in your query the param @Date will also add the time portion automatically to the result (place a breakpoint and have a look). Now as you supplied DateTimeStyles.AssumeUniversal the time is set to 00:00:00 UTC time which will be translated to the current timezone. (Being here in Australia puts that to 10:30:00).

sqlCommand.Parameters.AddWithValue("@Date", dt1.Date); //parsed date at midnight 00:00:00

Now IMHO using a stored procedure would be your best bet as you can use a single query to query and insert.

A sample procedure such as.

CREATE PROCEDURE InsertNewRecord
    @TagNumber int,
    @Date datetime
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT TOP 1 1 FROM ResultsTable WHERE [Date] = @Date AND TagNumber = @TagNumber)
        INSERT INTO ResultsTable (TagNumber, [Date]) VALUES (@TagNumber, @Date)
END
GO

Next you can easily call this (note just using test data).

var tagNumber = "111";
var date = DateTime.ParseExact("28.01.2017", "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal);

using(var con = new SqlConnection(connectionString))
{
    using(var cmd = new SqlCommand("EXEC InsertNewRecord @TagNumber, @Date", con))
    {
        cmd.Parameters.AddWithValue("@TagNumber", tagNumber);
        cmd.Parameters.AddWithValue("@Date", date.Date);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

}

As you see from the stored procedure we are simply querying first (using the NOT EXISTS and selecting a true result limiting to a single row for performance. SELECT TOP 1 1 FROM .. returns a single row of 1 if both the tag number and date exist on a record.

Now you could also change your data type from datetime to date which eliminates the time portion of your @Date paramater. However this will require you to ensure your data is clean and the table would have to be rebuilt.

One final option is to cast your datetime fields to a date in your query and change the @Date paramter to a type of date then check if they are equal such as.

ALTER PROCEDURE InsertNewRecord
    @TagNumber int,
    @Date date
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT TOP 1 1 FROM ResultsTable WHERE cast([Date] as date) = @Date AND TagNumber = @TagNumber)
        INSERT INTO ResultsTable (TagNumber, [Date]) VALUES (@TagNumber, @Date)
END
GO

For completness if for some reason you dont want to use a Stored Procedure the following will check if the record exists (note using the .Date property).

using (var con = new SqlConnection(connectionString))
{
    bool exists = false;
    using(var cmd = new SqlCommand("SELECT TOP 1 1 FROM ResultsTable WHERE TagNumber=@TagNumber AND [Date]=@Date", con))
    {
        cmd.Parameters.AddWithValue("@TagNumber", tagNumber);
        cmd.Parameters.AddWithValue("@Date", date.Date);

        con.Open();
        var result = cmd.ExecuteScalar(); //returns object null if it doesnt exist
        con.Close();

        exists = result != null; //result will be one or null.
    }
    if (exists)
    {
        //INSERT RECORD
    }
}

Either way I would say the issue is lying on the time portion of the data however without more information we can only guess.

---

Related Topics