Click here to Skip to main content
15,880,299 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
See more:
Hi, I am a not much aware of sql so please help me ,I need to avoid duplicate records while inserting a table in sql



Here is my code below



ALTER procedure [dbo].[sp_InsertBankName]
(
@bankname as varchar(50)
,@banknamea as nvarchar(50)
)
as
begin
declare @bankid as varchar(10)
select @bankid=isnull(max(convert(int,bankid)),0)+1 from bankname
insert into bankname
(bankid,bankname,banknamea)
values (@bankid,@bankname,@banknamea)
end
Posted

try below modified code :

SQL
alter procedure [dbo].[sp_InsertBankName]
(
    @bankname as varchar(50)
    ,@banknamea as nvarchar(50)
)
as
begin
    declare @bankid as varchar(10)
    declare @bCount as int

    select @bCount = count(bankname) from bankname where bankname = @bankname

    if @bCount = 0
    begin
        select @bankid=isnull(max(convert(int,bankid)),0)+1 from bankname
        insert into bankname
        (bankid,bankname,banknamea)
        values (@bankid,@bankname,@banknamea)
    end
end
 
Share this answer
 
Comments
ManojMurali 2-May-13 7:19am    
Thank dude its working

weather can we give message that its already exists like here in sql
In your table bankname Make BankId Primarykey and Autoincreament
SQL
ALTER procedure [dbo].[sp_BankName]
 (
 @BankId as int,
 @bankname as varchar(50),
 @banknamea as nvarchar(50)
 )
 as
 begin

    if exists(select * from bankname where bankid=@BankId) 
    begin
        insert into bankname(bankname,banknamea)
                    values (@bankname,@banknamea)
    end
    else
    begin
        Update bankname set bankname=@bankname, banknamea=@banknamea where bankid=@bankid
    end
end

in code,
while new record pass bankid 0 it will insert record
when update pass value of bankid which record you are updating
Happy Coding!
:)
 
Share this answer
 
Hi...

First select all records from that table, put into a datatable or dataset.
Now u will compare a particular field(means based on-username or password) from dataset,when click insert button - if that record is exists display an errmsg otherwise insert cmd is excute.
In this case duplicate values are eliminated by condition.

thank u
 
Share this answer
 
Try this way
ALTER procedure [dbo].[sp_InsertBankName]
(
@bankname as varchar(50)
,@banknamea as nvarchar(50)
)
as
begin
declare @bankid as varchar(10)
select @bankid=isnull(max(convert(int,bankid)),0)+1 from bankname

IF NOT EXISTS(SELECT 1
FROM dbo.bankname
WHERE bankid = @bankid)
begin

insert into bankname
(bankid,bankname,banknamea)
values (@bankid,@bankname,@banknamea)
end
end
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900