don''t want to insert same values 2 times.

Question

Post reply

don''t want to insert same values 2 times.

bluepaperbag

SSCrazy

Points: 2055
More actions
September 6, 2006 at 1:01 pm

#62040

create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as
begin
set nocount on
declare @sql varchar(700)
-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'
-- and a.id_town = + @tableName
-- print(@sql)
exec(@sql)
set nocount off
end
---------------------------------------------------
when i run this SP 2 times, it will insert the values 2 times.
what should i do if i don't want to insert the same values 2 times?
I'm confussed, Should i use update? any input will be appreciated.

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

I am assuming that id_town and id_voter are unique. so u need to add "NOT EXISTS" clause to you select statement.

Here you go:

begin

set nocount on

declare @sql varchar(700)

-- truncate table tempException

set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)

select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()

from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a

where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in

(select nm_street from HAVA_DCDE.dbo.state_streets ss)

and not exists (Select 1 from hava_dcde.dbo.STREET_EXCEPTION DEST where DEST.id_town = a.id_town and DEST.id_voter = a.id_voter)'

-- and a.id_town = + @tableName

-- print(@sql)

exec(@sql)

set nocount off

end

Hope this helps.

Thanks

Sreejith

Bill Scott SSCrazy Points: 2338 More actions · Answer 2

You could add an index to your table that requires the keys to be unique. If you then try to insert a duplicate key, the server will refuse to add the record and throw an error you could catch.

bluepaperbag SSCrazy Points: 2055 More actions · Answer 3

how can i write the code to catch the error?

is it somehting like

if @@error <> 0

print 'error'

Bill Scott SSCrazy Points: 2338 More actions · Answer 4

yes, each error has a specific number, i don't recall off the top of my head which number is duplicate key violation but we use code that looks like

if @@rowcount <> 1 or @@error <> 0 begin

-- handle error here

return 1

end -- return error

you can examine the value of @@error to find out what error number you have

bluepaperbag SSCrazy Points: 2055 More actions · Answer 5

i think its 2627

so it will be like

if @@error = 2627

print 'error'

bluepaperbag SSCrazy Points: 2055 More actions · Answer 6

bluepaperbag

SSCrazy

Points: 2055

May 1, 2007 at 9:03 am

#703557

1