recognizing duplicate record

[tek1ket]

how is it possible to prevent inserting a record when it has a duplicate field in it

 

[sqlfable]

primary key?

 

[markros]

You can use Stored Procedure for your inserts and verify if the value you want to insert already exists before inserting and Raiserror in case it exists.

In SQL Server 2008 you can also take a look into the new MERGE command.

 

[tek1ket]

it is not primary key
is it possible ti give me an example for that?

 

[TysonLPrice]

There's probably lot's of ways to do it. Here's a simple version:

Create table #TestIT(No_Key varchar(6))
Insert into #TestIt(No_Key) Values('Value1')
Insert into #TestIt(No_Key) Values('Value2')
Insert into #TestIt(No_Key) Values('Value3')

If not Exists (select No_Key from #TestIt  where No_Key = 'Value4')
    Insert into #TestIt(No_Key) Values('Value4')
else Select 'Duplicate key - No Insert'

If not Exists (select No_Key from #TestIt  where No_Key = 'Value4')
    Insert into #TestIt(No_Key) Values('Value4')
else Select 'Duplicate key - No Insert'

Select * from #TestIt

Drop table #TestIt

 

[r937]

i don't understand, tyson, why you would want to write code for something that the database system offers straight outta da box -- a UNIQUE index



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[TysonLPrice]

r937,

I wouldn't and never have. I prefer identity columns. I was answering what I thought the OP asked. Preventing duplicates on a non-key column.

 

[r937]

you can prevent duplicates in a non-PK column with a UNIQUE index

of course, i suppose you might say that creating a UNIQUE index on a column makes it a key, but that's really just a quibble



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

What if you need combination of multiple columns to be unique?

 

[r937]

a combination of columns may have a UNIQUE index

this is readily apparent if you look up CREATE INDEX in da manual

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Good point, but by the funny coincedence (and this is happening too often) the same question was just asked on another forum I visit, so I got the exact command without even checking the help.

 

[gmmastros]

Weird.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

In my practice it happens all the time. May be because I visit too many forums

 

[tsuji]

Exact command? what are they talking about? Is that what we call socializing and shouldn't be taken to mean anything?

 

[markros]

I meant this thread

http://forums.asp.net/t/1419391.aspx

Sorry, I use any opportunity for idle talk.