Before insert how to check the records if it exists or not

vijay reddy

10y1k

hi,

I created two tables like

create Table A (pid bigint, pname varchar, psource varchar(10))

create Table #temp (id bigint, name varchar, source varchar(10))

table A having lakhs of rows and table #temp having lakhs of rows

i want to check the records based on #temp table and insert into Table A.

how to check the records before insert values from #temp table to table A

insert into A

(pid,

pname,

psource

)

select

id,

name,

source

from #temp

Please give me the solution for the above query.

9 Replies

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

Tapan Patel10y ago
Hivijay reddy,
You can use something as below.
INSERT INTO TABLE_A
SELECT COLUMNS FROM TABLE_B
WHERE NOT EXIST(SELECT YOUR_UNIQUE_KEY FROM TABLE_A WHERE UNIQUE_KEY="VALUE OF UNIQUE_KEY")
Sanjeev Sriramoju10y ago
Take table 1
Take table 2
you want to insert into table 2 which are not in table 1. we can use "NOT IN " clause.Below is the sql query to resolve it
Insert into table 1
Select * from table 2 where ID not in (select IDfrom table1)
Vishal Jadav10y ago
Hi,
If your #temp table contains duplicate ids then you need to remove them first.
then
insert into A
(pid,
pname,
psource
)
select
id,
name,
source
from #temp where id not in (select pid from A)
.
If you have any query please let me know.
Hope it helps.
Thanks.
Jisny Av10y ago
1. if(EXISTS
2. (SELECT column-name
3. FROM table-name
4. WHERE condition))
5. begin
6. insert into table-name values(val1,val2,...)
7. end
vijay reddy10y ago
hi vinay,
as per your suggestion
if not exists( select * from #temp where id=1111)
begin
insert into A
(pid,
pname,
psource
)
select
id,
name,
source
from #temp
end
i want to compare with id = pid before inserting, how can i do it
Vinay Singh10y ago
It's the pid
vijay reddy10y ago
hi vinay,
thanks for your response,
in that id is having more than 1000 records how can i check
thanks
vijay
Vinay Singh10y ago
hi check the query
if not exists( select * from #temp where id=1111)
begin
insert into A
(pid,
pname,
psource
)
select
id,
name,
source
from #temp
end
ketan borsdiya10y ago
Hi Vijay,
You may use Merge in sql. Check below merge statements it may help you.
CREATE PROCEDURE PRC_Test
@table As dbo.UDTT_Test READONLY
AS
BEGIN

MERGE AS T
USING @table AS S
ON T.MDN = S.MDN
WHEN NOT MATCHED THEN
INSERT (MDN,RES1,RES2,RES3,ChangeMode)
VALUES (S.MDN,S.RES1,S.RES2,S.RES3,S.ChangeMode)
WHEN MATCHED THEN
UPDATE SET
MDN=S.MDN,
RES1=S.RES1,
RES2=S.RES2,
RES3=S.RES3,
ChangeMode=S.ChangeMode;
END