Fastest way to insert the rec in table

Question

Fastest way to insert the rec in table

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
February 1, 2017 at 9:54 am

#340100

I have 2 tables one is dbo.emptemp and another is dbo.emp
I am trying to join these two tables and see if record is already exists or not in dbo.emp table if exists I don't want to insert it.
select * from dbo.emptemp innerjoin dbo.emp
where not exists(select * from dbo.emp where id = @id)
Is this is the correct way to join and check it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99127 More actions · Answer 1

Simply doing:
SELECT * FROM emptemp et WHERE et.empid NOT IN (SELECT sq.empid FROM emp sq)
Would work to get the list.

You could, however, use a MERGE (Transact-SQL) statement to check and insert at the same time.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

INSERT INTO emp (column_list)

SELECT column_list FROM emptemp

EXCEPT

SELECT column_list FROM emp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

Based on this statement if I don't find any rec in main table I can do insert right?

SELECT * 
FROM emptemp et
WHERE et.empid NOT IN (SELECT sq.empid FROM emp sq)

Thom A SSC Guru Points: 99127 More actions · Answer 4

IF both tables have identical DDL, AND you're not using an IDENTITY Column, you could simply do:
INSERT INTO dbo.emp SELECT * FROM dbo.emptemp et WHERE et.empid NOT IN (SELECT sq.empid FROM dbo.emp sq)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk