INSERT INTO TABLE IF NOT EXISTS RECORD in table Is Inserting Multiple times single record

Question

0.00/5 (No votes)

See more:

hi,
The below is the sql statement that inserts the record if only the record is not exists in table.. but the statement i written is inserting multiple times the same record... please help me to resolve my problem..

SQL

INSERT INTO TableName(AccountNo,Customer,ContactNo) select 'AP1234','Saketh','984822338'  FROM TableName WHERE NOT EXISTS(Select * From TableName Where AccountNo='AP1234');

Posted 26-May-15 21:07pm

CHAITANYA KIRAN KASANI

Updated 27-May-15 1:46am

v3

Add a Solution

Comments

Michael_Davies 27-May-15 3:26am

You do not need the NOT EXISTS as your INSERT is inserting literal values from the SELECT, you would only need the NOT EXIST if the select had a where clause to find a record from a table and that record did not exist.

Here is how you SQL literally looks:

INSERT INTO TableName (AccountNo,Customer,ContactNo) VALUES 'AP1234','Saketh','984822338';

As you can see it will always insert the exact same record each time you run it.

Andy Lanng 27-May-15 4:30am

I concur with Michael_Davies. You will have to perform two queries. One to find out if the record already exists and the second to insert it. If this was a stored proc it would look a bit like:
IF NOT EXISTS(Select * From TableName Where AccountNo='AP1234')
BEGIN
INSERT INTO TableName(AccountNo,Customer,ContactNo) select 'AP1234', 'Saketh', '984822338')
END

2 solutions

Solution 1

try this

hope it would help you

INSERT INTO TableName(AccountNo,Customer,ContactNo) select 'AP1234','Saketh','984822338' FROM TableName WHERE
'AP1234'+'Saketh'+'984822338' NOT In (Select distinct coalesce(AccountNo,'')+coalesce(Customer,'')+coalesce(ContactNo,'') From TableName);

Posted 26-May-15 22:37pm

Salman622

Comments

Andy Lanng 27-May-15 4:56am

How is this any different from what he has already?
If it is then I genuinely would be interested in understanding how.

Salman622 27-May-15 5:23am

no there is no difference if you are inserting through literal values

but it would be usefull for bulk insertion for that query would like

INSERT INTO TableName1(AccountNo,Customer,ContactNo) select column1,column2,column3 FROM TableName WHERE
coalesce(column1,'')+coalesce(column2,'')+coalesce(column3,'') NOT In (Select distinct coalesce(AccountNo,'')+coalesce(Customer,'')+coalesce(ContactNo,'') From TableName1);

this query would insert only record which are not in TableName1

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)

CHAITANYA KIRAN KASANI · Accepted Answer · 2015-05-27T01:41:00

i found myself after some workouts..
I Have To add DISTINCT to select command

now it Inserting The Record Single Time Only,(npt multiple times by using DISTINCT)

SQL

INSERT INTO TableName(AccountNo,Customer,ContactNo) select DISTINCT 'AP1234','Saketh','984822338'  FROM TableName WHERE NOT EXISTS(Select DISTINCT AccountNo From TableName Where AccountNo='AP1234');