how to stop duplicating

Question

Post reply

how to stop duplicating

sampathsoft

SSC Eights!

Points: 855
More actions
March 10, 2008 at 5:33 am

#69987

Heloo all,

how i show messege if i entering details already stored in my sqlserver 2000 database.

This's my problem.

I have insert some customer details.But if i try to add same customer details again and again i want to show it already inserted. So let me know how i do that thing.................

Now im doing it like this

SELECT *

FROM tblInsertDetails

WHERE (Status = 1) AND (RefNumber LIKE @InsertID)

Use select statement and getting some dataset and if that dataset is null then i gave permission to insert that record. But i want to learn it better way to do inside the sql or some way.

thankyou

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Lookup RAISERROR in Books Online... that should do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

goodguy SSCertifiable Points: 5456 More actions · Answer 2

goodguy

SSCertifiable

Points: 5456

March 11, 2008 at 1:20 am

#787807

Why don't you make the field a PRIMARY KEY or set it as UNIQUE? Then SQLServer will validate automatically and reject a new insertion if its exists.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Yes, that would be the safe guard... but, I recommend that you don't rely on that safeguard as a programming tool... takes a comparitively long time to have an error rather than check for existance and insert only when unique... especially of batches of a couple thousand where the whole batch could be reject if one is not unique. Sure, the "Ignore Dupes" setting for unique indexes will keep the batch from rejecting, but again, relatively expensive and doesn't tell you a bloody thing about which rows were dupes.

And, such indexing actually slows down bulk inserts... a LOT! Best thing to do is import everything into a staging table with a surrogate PK and do a high speed dupe check (and other checks) to mark either bad or good rows. Then, use criteria to ignore "bad" rows.

I never import directly to final tables anyway. Causes too many locks on "active tables" while you're trying to validate rows. Allowing indexes to do your work for you is "programming by exception" and it will make for slow code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

goodguy SSCertifiable Points: 5456 More actions · Answer 4

Thanks, Jeff, for enlightening us. I do not know if I will encounter in a lifetime any of the issues you encounter every working day; your experience is of VLDB environments, mine is trifling in comparison. As I always say, I learn something new every day.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Thanks for the feedback, GoodGuy... but it's all the same... just bigger, that's all 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions