Insert into table if record don't exist

CHAITANYA KIRAN KASANI

11y1.2kSQL

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..

Hide Copy Code

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
good work for I've tested my query in my code dear.
This work better for me and also for other.
0
CHAITANYA KIRAN KASANI11y ago
hi upendra,

I had tried your query but it also inserting multiple times the single record....so i used DISTINCT
0
Upendra Pratap Shahi11y ago
use this if you have want to use your query-

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

this will work for you but performance is very slow when large of data in table because DISTINCT work row by row and this make slow your query so try to use below query:

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

If this is suitable for you mark the answer as Accepted Answer
-1
CHAITANYA KIRAN KASANI11y ago
i found myself after some workouts..
I Have To add DISTINCT to select command

now it Inserting The Record Single Time Only,

INSERT INTO TableName(AccountNo,Customer,ContactNo) select DISTINCT 'AP1234','Saketh','984822338' FROM TableName WHERE NOT EXISTS(Select DISTINCT AccountNo From TableName Where AccountNo='AP1234');
0
Upendra Pratap Shahi11y ago
INSERT INTO TableName(AccountNo,Customer,ContactNo)
select 'AP1234','Saketh','984822338'
FROM TableName m
WHERE NOT EXISTS
(
Select * From TableName t
Where t.AccountNo=m.AccountNo and AccountNo='AP1234'
);

this may help you..

If useful kindly accept the answer.
0
Dipankar Biswas11y ago
Hi... try it..

If Not Exists(select * from tablename where code='1448523')
Begin
insert into tablename (code) values ('1448523')
End
Or
insert into tablename (code)
Select '1448523' Where not exists(select * from tablename where code='1448523')

Thanks...
0