Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Data exists or not Expand / Collapse
Author
Message
Posted Friday, November 29, 2013 3:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1518486
Posted Friday, November 29, 2013 3:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
INSERT INTO TableB ...
SELECT ...
FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1518487
Posted Friday, November 29, 2013 3:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1518490
Posted Friday, November 29, 2013 3:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1518493
Posted Friday, November 29, 2013 3:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1518494
Posted Friday, November 29, 2013 5:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1518522
Posted Friday, November 29, 2013 5:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1518526
Posted Friday, November 29, 2013 5:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518608
Posted Saturday, November 30, 2013 10:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1518680
Posted Saturday, November 30, 2013 11:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518682
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse