Check Data exists or not

Question

Post reply

Check Data exists or not

kapil_kk

SSC-Insane

Points: 21316
More actions
November 29, 2013 at 3:19 am

#283038

HI all,
I have a scenario in which I have to transfer the data from tableA to tableB.
But before inserting in tableB, I have to check that record from tableA exists or not.
I know I can use IF EXISTS for that but if records are 1000 or more then it will take time to check whether data exists or not.
Is there any alternate way for this..
please suggest
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

INSERT INTO TableB ...

SELECT ...

FROM TableA

WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

GilaMonster (11/29/2013)
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

Sorry to mention that I also have update the record if data from tableA already exists in tableB....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Will it be good if I good if I use a MERGE for this and use that MERGE in a stored procedure, if MERGE not effects the performance ?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

kapil_kk (11/29/2013)
GilaMonster (11/29/2013)
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)
Sorry to mention that I also have update the record if data from tableA already exists in tableB....

Merge is designed to do exactly that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

GilaMonster (11/29/2013)
kapil_kk (11/29/2013)
GilaMonster (11/29/2013)
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)
Sorry to mention that I also have update the record if data from tableA already exists in tableB....
Merge is designed to do exactly that.

Will it effect the performance if data is more as it also process row by row only..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

kapil_kk (11/29/2013)
GilaMonster (11/29/2013)
kapil_kk (11/29/2013)
GilaMonster (11/29/2013)
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)
Sorry to mention that I also have update the record if data from tableA already exists in tableB....
Merge is designed to do exactly that.
Will it effect the performance if data is more as it also process row by row only..

Merge doesn't process row by row, no more than insert, update or delete do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

Be very careful if you decide to use MERGE. There are several CONNECT items that are open about it and, like anything else, if you don't obey some fairly strict common sense rules about how to use it, it will burn you with no errors reported.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

can you please tell me how merge doesn't process row by row...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 9

kapil_kk (11/30/2013)
can you please tell me how merge doesn't process row by row...

Actually, everything in SQL Server processes "row by row". If you let SQL Server do it, it's fast. If you try to tell SQL Server how to do it, it's slow. If you look at SELECT, INSERT, UPDATE, DELETE, or MERGE, do you see anything in there that would allow you to control the looping in the background?

MERGE is no different than the 4 other basic statements when it comes to processing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)