Check existing records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check existing records

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-06 : 21:27:36

Guys,

How do i check this records from temporary table to the main table if this records is exist? i have 100 records for temporary table and 100,000 for the main table. i want to display only the records that does not exist. thanks.

Select #VPTable --temporary table
Insert Into #VPTable (ESN) Values ('268435458414301470')
Insert Into #VPTable (ESN) Values ('268435457313676521')
Insert Into #VPTable (ESN) Values ('268435459605668423')

Here is my sample Script.

Select
es.esn_no,
tmp.esn
from tb_batch_esn as es
Left Outer Join #VPTable as tmp
On es.esn_no=tmp.ESN COLLATE Chinese_Taiwan_Stroke_CI_AS
group by es.esn_no, tmp.ESN

JOV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 22:07:59

[code]
Select
es.esn_no,
tmp.esn
from tb_batch_esn as es
Left Outer Join #VPTable as tmp
On es.esn_no=tmp.ESN COLLATE Chinese_Taiwan_Stroke_CI_AS
where tmp.ESN is null
group by es.esn_no, tmp.ESN
[/code]

or alternatively use NOT EXISTS

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-07 : 00:39:19

For NOT EXISTS, how to incorporate this?
Kind yopu give me sample KH. Thanks.

Select
es.esn_no,
tmp.esn
from tb_batch_esn as es
Left Outer Join #VPTable as tmp
On es.esn_no=tmp.ESN COLLATE Chinese_Taiwan_Stroke_CI_AS
where tmp.ESN is null
group by es.esn_no, tmp.ESN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-07 : 00:44:34

[code]
Select
es.esn_no
from tb_batch_esn as es
where NOT EXISTS
(
SELECT *
FROM #VPTable as tmp
WHERE es.esn_no = tmp.ESN COLLATE Chinese_Taiwan_Stroke_CI_AS
)
group by es.esn_no
[/code]

KH
[spoiler]Time is always against us[/spoiler]

victy23
Starting Member

3 Posts

Posted - 2011-12-07 : 02:09:48

Come on ,you can get it

Love smile,love life.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-07 : 06:38:22

Hi,

Do except or not in or not exists query from your main table with your temp table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources