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

Assist me the merge statement performance in sql server.. Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:32 AM
Points: 138, Visits: 387
Hi Everybody,

Im using following query, is there any other alternative way?


MERGE DistinctData AS TARGET
USING DataTable AS SOURCE
ON (TARGET.msisdn = SOURCE.Charged_party_number)
WHEN NOT MATCHED BY TARGET THEN
INSERT (msisdn)
VALUES (SOURCE.Charged_party_number);


Assist me the merge statement performance in sql server..
Post #1488402
Posted Monday, August 26, 2013 8:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 13,438, Visits: 12,300
vignesh.ms (8/26/2013)
Hi Everybody,

Im using following query, is there any other alternative way?


MERGE DistinctData AS TARGET
USING DataTable AS SOURCE
ON (TARGET.msisdn = SOURCE.Charged_party_number)
WHEN NOT MATCHED BY TARGET THEN
INSERT (msisdn)
VALUES (SOURCE.Charged_party_number);


Assist me the merge statement performance in sql server..


I am guessing by the topic that the performance of this is not acceptable? Without more details it is impossible to offer much help. Please see this article for best practices when seeking help on performance issues. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488408
Posted Monday, August 26, 2013 11:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:32 AM
Points: 138, Visits: 387
hi SSCrazy Eights,

I have 2 table say source and destination.

I'm trying to insert data from source table to destination table ,which are not available in the destination table.

this is the requirement, suggest me the best possible way.

Thanks many..
Post #1488556
Posted Tuesday, August 27, 2013 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 13,438, Visits: 12,300
vignesh.ms (8/26/2013)
hi SSCrazy Eights,

I have 2 table say source and destination.

I'm trying to insert data from source table to destination table ,which are not available in the destination table.

this is the requirement, suggest me the best possible way.

Thanks many..


Your description is not very clear. It sounds like a good candidate for the MERGE statement (which you said you used in your original post).


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488769
Posted Tuesday, August 27, 2013 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:32 AM
Points: 138, Visits: 387
Sean Lange (8/27/2013)
vignesh.ms (8/26/2013)
hi SSCrazy Eights,

I have 2 table say source and destination.

I'm trying to insert data from source table to destination table ,which are not available in the destination table.

this is the requirement, suggest me the best possible way.

Thanks many..


Your description is not very clear. It sounds like a good candidate for the MERGE statement (which you said you used in your original post).



Hi Sean Lange,

Forgot about the query and all that I have shared already...



create table SourceTable
(number int)

create table DestinationTable
(number int)

insert into SourceTable values(1)
insert into SourceTable values(2)
insert into SourceTable values(3)
insert into SourceTable values(4)
insert into SourceTable values(5)
insert into SourceTable values(6)


insert into DestinationTable values(1)
insert into DestinationTable values(2)
insert into DestinationTable values(3)


In the above sample, sourcetable contain values 1,2,3,4,5,6
And Destinationtable contains 1,2,3

I need a query to insert 4,5,6 alone from source table .

(ie) query should check whether the upcoming data is already there or not. if not insert else skip.

note : both tables are too huge. please suggest me best way to achieve the goal

hope now you could understand my requirement.

Thanks


Post #1488795
Posted Tuesday, August 27, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 13,438, Visits: 12,300
That helps clarify. I wouldn't use MERGE for this. I converted your sample ddl into temp tables so it is easier to work with in a test database.

create table #SourceTable
(number int)

create table #DestinationTable
(number int)

insert into #SourceTable values(1)
insert into #SourceTable values(2)
insert into #SourceTable values(3)
insert into #SourceTable values(4)
insert into #SourceTable values(5)
insert into #SourceTable values(6)

insert into #DestinationTable values(1)
insert into #DestinationTable values(2)
insert into #DestinationTable values(3)

select *
from #SourceTable
where number not in
(
select number
from #DestinationTable
)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488799
Posted Tuesday, August 27, 2013 8:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:51 AM
Points: 519, Visits: 175
Well worth a read when optimising for large datasets

http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx
Post #1488801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse