SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check Data exists or not


Check Data exists or not

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5136 Visits: 2767
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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212725 Visits: 46259
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>Wink

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
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5136 Visits: 2767
GilaMonster (11/29/2013)
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>Wink

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
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5136 Visits: 2767
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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212725 Visits: 46259
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>Wink

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
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5136 Visits: 2767
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>Wink

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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212725 Visits: 46259
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>Wink

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
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204723 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5136 Visits: 2767
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
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204723 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search