Search for duplicates before doing INSERT

[avu]

Hello,

Can anyone advice if it's possible to insert a record (name + address) into a table where it would search the table first for duplicate 'name' or 'address'?

I normally run 2 procedures in which first procedure searching for duplicate. If it's empty, then call 2nd procedure to do the INSERT.

Can this be done in one procedure or sql statement?

Thanks much in advance.

Allan

 

[swampBoogie]

Yes, somewhat like

if not exists (select * from t
where adress = @adress ...  )
begin
   insert into t ...
   set @e = @@error
   if @e <> 0 goto errorLabel
end

is one way.

 

[philhege]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_testaddress]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[tmp_testaddress] (
[ts_pk] [int] IDENTITY (1, 1) NOT NULL ,
[ts_name] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ts_address] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
end
GO

declare @L_cname char(64), @l_caddress char(128)
set @L_cname = 'Joe Person'
set @l_caddress = '144 Main St.'

if not exists(select ts_pk from tmp_testaddress where ts_name = @l_cname and ts_address = @l_caddress)
begin
insert into tmp_testaddress(ts_name, ts_address)
VALUES(@l_cname,@l_caddress)
end

select * from tmp_testaddress

Change the values of @l_cname and @l-caddress (or not) to demonstrate.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[avu]

Thanks both of you very much for your help.

Best regards.