Assist me the merge statement performance in sql server..

Question

Assist me the merge statement performance in sql server..

vignesh.ms

Hall of Fame

Points: 3456
More actions
August 26, 2013 at 8:30 am

#279090

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..

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

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..

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RandomEvent SSCrazy Points: 2963 More actions · Answer 6

Well worth a read when optimising for large datasets

http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx