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 |
|
johnsql
Posting Yak Master
161 Posts |
 Posted - 2007-10-15 : 08:59:47
|
| Hi, Please give me any idea how to PREVENT duplicate rows in a table. I have a table named "myTable". The table has column "ID" as a primary key. Other columns are normal. The problem is data in some columns of some rows are duplicated in that table like:ID ClientID LastName FirstName Phone1 phone2 ----------- ----------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- 3735 1440 White Deborah 951-533-0486 3739 1440 White Deborah 951-533-0486 3746 1440 White Deborah 951-533-0486 3978 1440 White Deborah 951-358-0250 951-533-04863985 1440 White Deborah 951-358-0250 951-533-0486Here are the problem of duplicate:- The data of columns of "LastName", "FirstName", "Phone1" of the rows whose ID are 3739 and 3746 repeat the data of the same columns of the row of ID of 3735.- The data of columns of "LastName", "FirstName", "Phone1", "Phone2" of the rows whose ID is 3985 repeats the data of the same columns of the row of ID of 3978. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-15 : 09:46:11
|
Try add a UNIQUE CONSTRAINT or possibly an UNIQUE INDEX.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-15 : 09:46:54
|
| Add a unique index - that will give you a falure.You can use a trigger to prevent the insert/update without a failure if you wish.Other methods will depend on how the data gets into the table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-10-15 : 12:18:58
|
| Thank Peso and NR very much for your helpful solutions. |
|
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-10-15 : 12:36:15
|
quote:
Originally posted by johnsql
Thank Peso and NR very much for your helpful solutions.
One more thing, If I'd like to use a trigger, how can I write such scripts?Thanks. |
|
|
|
|
|
|
how to prevent duplicate rows for a table