| Author |
Topic |
|
sebastian11c
Posting Yak Master
129 Posts |
 Posted - 2012-04-17 : 12:55:42
|
| i hace a table with 4 columns one column is an identity colums the others are dataidclient (identity) city name lastname phonehow could i avoid to inser duplicate records on my table, this issue is killing mea trigger?any idea will be realli appreciate many many thanks in advanced |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-17 : 13:29:36
|
| what according to your are duplicates? first explain that with sample data for us to understand------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-17 : 14:47:07
|
| HI, OBVIOUSLY the identity column cannot be duplicate, so what im trying to say is the other columns have the same dataexampleidclient (identity) city name lastname phone1 miami peter smith 78550629952 miami peter smith 7855062995thats what im trying to avoid but i dont know how,,( i have 2 record with different idclient(because is identity, ) but all other columns are exactly the samei dont want to save same recordsany help will be appreciatemany many thankskind regards |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-17 : 14:53:52
|
you want to remove the existing duplicates alone or you want a logic to check for duplicates in future inserts as well?how is the table getting populated?for removing existing duplicates, you can do likeDELETE tFROM (SELECT ROW_NUMBER() OVER (PARTITION BY city, name, lastname,phone ORDER BY idclient) AS Rn FROM Table)tWHERE Rn >1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-17 : 18:20:10
|
| hi visakh16 thanks for your helpby the way , how do you know all the answer? also i need a logic to check for duplicates in future inserts as wellthanks in advanced |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-18 : 00:29:41
|
quote:
Originally posted by sebastian11c
hi visakh16 thanks for your helpby the way , how do you know all the answer? also i need a logic to check for duplicates in future inserts as wellthanks in advanced
Mostly by experience and learning from others here  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-18 : 00:30:19
|
quote:
Originally posted by sebastian11c
hi visakh16 thanks for your helpby the way , how do you know all the answer? also i need a logic to check for duplicates in future inserts as wellthanks in advanced
for that easiest way is to set a unique constraint as Tara suggested------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-18 : 09:19:01
|
| im not sure how to set a unique constraint??could you help me to do properlythanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-18 : 15:55:33
|
| i wll add more complex, im inserting 300 records with one query, how could i do to insert the record that arent duplicates (not exist in my table)because im trying to insert 300 records on my tabe but i get and error "cannot insert duplicated key, violation unique key constraint" and no one is instered in my table,that i need its something to let me insert the records that dont exist in my table , and avoid to insert the duplicates, but the end user dont get any warning messageany help will be appreciatethanks in advanced |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-18 : 16:18:01
|
| im doing the insert from an appthanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-18 : 18:48:26
|
| hi, i cannot change the app, so i cant insert one by one,,also , could you give me with my table "clients" an example hor could i do this """ Insert them into a staging table and then use INSERT/SELECT * FROM NOT EXISTS to move the data from staging table to your actual table""table CLIENTSidclient (identity) city name lastname phone1 miami peter smith 78550629952 miami peter smith 7855062995many many thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:22:59
|
quote:
Originally posted by sebastian11c
im doing the insert from an appthanks
does app call insert procedure for data population? do you've any control over it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-04-19 : 09:10:38
|
| the apps uses an stored procedure to insert the data into the table,insert one by one, but send 100 rows to insert |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
avoid insert duplicate record