Results 1 to 11 of 11

Thread: duplicate data

  1. #1
    Join Date
    Mar 2007
    Posts
    45

    Unanswered: duplicate data

    Hello all,

    I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database.
    I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added.

    Can anyone help?

    Thanks

  2. #2
    Join Date
    Jul 2007
    Posts
    96
    Could you provide us the DDL?

  3. #3
    Join Date
    Mar 2007
    Posts
    45
    Well lets see....

    I am not an expert in database programming but I am learning...

    Ok,

    here is some psuedocode for a general idea

    create procedure insertData
    (
    var 1,
    var 2,
    var 3
    )
    set nocount on
    as
    insert into <table> values <1, 2, 3>
    select * from <table>

    for(i < list) --perform test and search the database
    (
    array < list> or perhaps a resursion statement
    if (list == var1, var2, var3)
    return -1
    else return 0
    )

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't have to check for duplicates, the database can do this for you automatically

    just declare a unique constraint on the column(s) that you want to be unique

    vwalah!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    45
    well thanks,

    that was easy!

  6. #6
    Join Date
    Jul 2007
    Posts
    96
    There are several ways to prevent duplicate data from being inserted into a table. One of them would be using a UNIQUE constraint.

    But, before you get into the solution I'd like to ask you to provide us with the real table structure, some sample data, as well as the business rules you are trying to enforce. All this because there's also the chance that you are using an inadequate table design to fullfill your needs.

  7. #7
    Join Date
    Mar 2007
    Posts
    45
    This is just one table with the fields: year, month, product type, product amount

    Now the table does not have a unique field, product type is a listing of only five categories such as: apples, pears, peaches, grapes, and oranges

    So really there is no unique field.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my opinion: year, month, product type are unique, and should be defined as the primary key

    vwalah! no need to check for dupes, the database will do it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Posts
    45
    But you could have data stating the same year, month, and product type, right?

  10. #10
    Join Date
    Mar 2007
    Posts
    45
    since this is just one table why not set all of the fields as a primary key?

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *shakes head*
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •