avoid duplicate records while inserting a table in sql

Question

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 2-May-13 0:44am

ManojMurali

Add a Solution

4 solutions

Solution 3

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!
:)

Posted 2-May-13 1:04am

Aarti Meswania

Solution 4

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

Posted 2-May-13 2:53am

Murali0195

Solution 1

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

Posted 2-May-13 0:54am

n2012

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bhushan Shah1988 · Accepted Answer · 2013-05-02T01:00:00

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