Click here to Skip to main content
14,937,524 members
Please Sign up or sign in to vote.
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
Updated 27-May-15 1:46am
v3
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

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);
   
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
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');
   
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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