insert statement

  • Hello Everyone,

    I have a table with say 4 columns.

    A      B    C    D

    I want to insert a row in this table in such a way that the combination of values in column A   B  and  C should be unique.

    For Example: values for A  B  C  D should accept

    1 2 3 4

    1 1 2 5

    but it should not accept 1 2 3  6 again since 1 2 3 combination for A B C already exist.

    How do i approach this.

    Please help me

     

     

  • Create composite unique index on columns A,B,C (not necessarily in this order - use the most selective column as first).

    When inserting, check whether a row with the same values exists or not. You can use either WHERE NOT EXISTS, or a LEFT JOIN to the table you are inserting into with accompanying WHERE ... IS NULL. I prefer the variant with IS NULL. (If you don't check anything, insert will fail if it would result in a duplicate).

    INSERT INTO my_table (a,b,c,d)

    SELECT col1, col2, col3, col4

    FROM other_table o

    LEFT JOIN my_table mt ON o.col1=mt.a AND o.col2=mt.b AND o.col3=mt.c

    WHERE mt.checkcol IS NULL

    "checkcol" can be any column that does not allow NULLs; if for example my_table has IDENTITY column, this would be a good candidate.

    And this is the other possibility, handy if you insert values of variables - but usable with columns (as above) as well.

    INSERT INTO my_table (a,b,c,d)

    SELECT @a, @b-2, @c, @d

    WHERE NOT EXISTS (select * from my_table where a = @a and b = @b-2 and c = @c)

  • Hi,

      U can use Indexes for that. Go to manage indexes & create new index using column a,b, c & click on "unique values check box".

    u will get ur desired result

  • Thank you Vladan , Anurag.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply