Link to home
Create AccountLog in
Avatar of gvamsimba
gvamsimbaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Tsql inserting dates into table

Hi I have a table with columns id, custid, periodstartdate and periodenddate. Both these date columns are only dates ( no times are recorded)
 
I am inserting data into this table on a daily basis via SQL job. But I need to make sure that I am not inserting a duplicate periodstartdate and periodenddate
For custid.

For example if custid 1 has already got a periodstartdate as '10-June -2011' and periodenddate as  '09-June-2012' then my insert should not insert this period into those date columns for custid 1.

Can any one please give me that where condition for that check ?

Many thanks
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

You need to define a unique index on those columns and use insert ignore or insert on duplicate key update
http://stackoverflow.com/questions/7775327/insert-ignore-unique-index
The best way is to create a unique contraint on the combination of the column i.e. custId, periodStartDate and periodEndDate.
Avatar of gvamsimba

ASKER

Hi is there any reason why I cannot use left outer join on this table  and then give a where condition ? as my insert query Is getting data from other tables.

Thanks
That's fine. Once you get the unique constraint you will not be able to put duplicate data in the table.
Hi for some reason, I am asked not to put any unique contraint on this table for some reason.

so, now my insert script has to take so that duplicate dates for an application are not inserted ?

Can any one please provide me with that condition ?

Thanks
Basically it's an
If (exists select blah)
Update
Else
Insert
<< I am asked not to put any unique contraint on this table for some reason>>
Can be for not having 'duplicate constraint'-errors
   but this can be countered by capturing the error and seeing that this is handled well
or not wanting the unique-constraint having to slow down insert
    but if you must replace that with own code that's also slowing down

Question is what must happen if there are duplicates: give error or just ignore what you want to insert?
just ignore what you want to insert...
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account