Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajdaksha
Aged Yak Warrior
595 Posts |
 Posted - 2009-07-13 : 04:39:07
|
HiI have a table with 3 columns CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);Question :Column C2 should be unique If I try to insert duplicate record in the column , it should not throw the error, it should insert other record(s) except duplicate record.How to achieve this?NOTETable already have one record with c2 column value as ‘abc’Am trying to insert the following recordSelect ‘a’, ’abd’, getdate() union all --1Select ‘a’, ’abc’, getdate() union all --2Select ‘a’, ’abe’, getdate() --3 It should not throw any error, but it insert only two record except second one (second one violate)There should not be any check using the script …..  -------------------------R.. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-13 : 04:42:59
|
1. Write an INSTEAD OF INSERT trigger2. Write an AFTER INSERT trigger3. Write an unique index over c2 column with IGNORE_DUP_KEY attribute on indexYou can do any of the three suggestions.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-13 : 05:43:41
|
| how it is possible by using trigger-------------------------R.. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-13 : 06:44:47
|
With the INSTEAD OF TRIGGER, you can do a NOT EXISTS when inserting the new records.With the AFTER TRIGGER, you can remove all duplicate recordsWith the unique index with IGNORE_DUP_KEY, the database will take care of it for you.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-13 : 07:03:50
|
| thanks peso-------------------------R.. |
|
|
|
|
|
|
|
|
QUERY