How to split and later reunite a large MS SQL table

Tags:
Informatica
SQL Server
Sir I am stuck in a logic. I have dynamic tables. Let's say one large data Big_Table is stamped with profile 1,2,3,....n So I create table profile1, profile2,... profilen (example profile1 :- select * from Big_Table where rule = profile1)
table data example :-
table_profile1
transaction_id profile priority
1 1 1
2 1 1

table_profile2
transaction_id profile priority
1 2 2
3 2 1
Now lets say we got single transaction in Big table is repeated in table_profile1 and table_profile2 So I need to pick all data of table_profile1,table_profile2,table_profilen with lower Priority So here in target transaction_id 2 is repeated in profile1 and profile2 table so final table will have only transaction id 2 with lower priority of profile 1 as its 1
Big_table_stamped
transaction_id profile priority
1 1 1
2 1 1
3 2 1
So in final stamped table Big_table_stamped only query I'm dynamically creating is:
select transaction_id,profile,priority
from (
select row_number() over (partition by a.transaction_id order by a.priority asc) r
a.*
from (
select * from table_profile1
union all
select * from table_profile2
union all
.
union all
select * from table_profilen
) a
) data
where data.r=1
)
But this is slow. Should I join all tables or what should be my approach? I don't have much time.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: