amukta
asked on
How do I update a column in a table on one server by doing a left join of tables another server in SSIS using look up or any other method and no linked server
Hi!
A column in one table on say server 1 needs to be updated by doing a left join of 4 or 5 tables on another server, say server2. Currently, I'm using an update statement in an Execute SQL task of SSIS and a 4 part naming convention to join the tables on Server 2, with server1(linked server concept),to achieve this. How can I do this without using a linked server in the joins and by using look up transformation?
I'm doing something like this:
Update table 1 --table 1 is on server 1 and database1
set a.col1=b.col1
from a
left join (select
C.COL1,
C.COL2,
SUM(C.COL3),
C.COL4,
C.COL5
FROM (
SELECT
D.COL1,
E.COL2,
F.COL3
ROWNUMBER() OVER(PARTITION BY E.COL1, E.COL2,E.COL3,E.COL4,F.COL
ORDER BY E.COL1,E.COL2,E,COL3,E,COL
FROM TABLE3 E
LEFT JOIN server2.database2.TABLE 4 F ON E.COL1=F.COL1--server 2 is the linked server
INNE R JOIN server2.database2.TABLE 5 G ON F.COL1=G.COL1
LEFT JOIN server3.database2.TABLE 7 H ON G.COL1=F.COL1
LEFT JOINserver4.database2.TABL
LEFT JOIN -------
-------) C
GROUP BY C.COL1,C.COL2,C.COL4,C.COL
on a.col1=b.col1
a.col2=b.col2
a.col4=b.col4
a.col5=b.col5
Can someone please help me using look up transformation or any other method in ssis, to avoid using linked server? Greatly appreciate your help! Thanks a million in advance!
A column in one table on say server 1 needs to be updated by doing a left join of 4 or 5 tables on another server, say server2. Currently, I'm using an update statement in an Execute SQL task of SSIS and a 4 part naming convention to join the tables on Server 2, with server1(linked server concept),to achieve this. How can I do this without using a linked server in the joins and by using look up transformation?
I'm doing something like this:
Update table 1 --table 1 is on server 1 and database1
set a.col1=b.col1
from a
left join (select
C.COL1,
C.COL2,
SUM(C.COL3),
C.COL4,
C.COL5
FROM (
SELECT
D.COL1,
E.COL2,
F.COL3
ROWNUMBER() OVER(PARTITION BY E.COL1, E.COL2,E.COL3,E.COL4,F.COL
ORDER BY E.COL1,E.COL2,E,COL3,E,COL
FROM TABLE3 E
LEFT JOIN server2.database2.TABLE 4 F ON E.COL1=F.COL1--server 2 is the linked server
INNE R JOIN server2.database2.TABLE 5 G ON F.COL1=G.COL1
LEFT JOIN server3.database2.TABLE 7 H ON G.COL1=F.COL1
LEFT JOINserver4.database2.TABL
LEFT JOIN -------
-------) C
GROUP BY C.COL1,C.COL2,C.COL4,C.COL
on a.col1=b.col1
a.col2=b.col2
a.col4=b.col4
a.col5=b.col5
Can someone please help me using look up transformation or any other method in ssis, to avoid using linked server? Greatly appreciate your help! Thanks a million in advance!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I can see from your query you deal with 4 servers and it is also wrong. This part particularly:
SELECT
D.COL1,
E.COL2,
F.COL3
ROWNUMBER() OVER(PARTITION BY E.COL1, E.COL2,E.COL3,E.COL4,F.COL
ORDER BY E.COL1,E.COL2,E,COL3,E,COL
FROM
...
I expect a comma before ROWNUMBER, which should actually be ROW_NUMBER.
SELECT
D.COL1,
E.COL2,
F.COL3
ROWNUMBER() OVER(PARTITION BY E.COL1, E.COL2,E.COL3,E.COL4,F.COL
ORDER BY E.COL1,E.COL2,E,COL3,E,COL
FROM
...
I expect a comma before ROWNUMBER, which should actually be ROW_NUMBER.
ASKER
This was just the pseudocode that I've put in for the question. However, my code has no syntax errors and works fine. I was asked not to use any staging table or anything as such. I'd need to replicate the above update
with the look up transformations. Could you please let me know , how I can perform this update using look up transformations?
with the look up transformations. Could you please let me know , how I can perform this update using look up transformations?
ASKER
There's a typo mistake, for server 3 and server 4, they are all server 2 in the subquery.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
so you want to refer to a remote server in your query, which is perfectly possible using linked server.
are you currently using that? if yes (as that does not use staging tables), I would not understand the question;
if no, you only need to set up the linked server (but then I don't understand how you could claim the sql works)
please clarify
are you currently using that? if yes (as that does not use staging tables), I would not understand the question;
if no, you only need to set up the linked server (but then I don't understand how you could claim the sql works)
please clarify
If your problem is performance then you should understand that when you refer to a table through a linked server and used it on the current host server to join that WILL cause performance problems because the whole table has to be accesses and made available over the network and brough over and then joined on the current host(server1). The best way to deal with this issue is to execute the code that references objects on the linked server on that server first and only bring over what you need so all those joins between tables on server2.database2 will take place on server2 and only the result will be brought over.
Basically what you should do is to create a view on the server 2 with all those table and access THAT using the linked server. Something like this:
Basically what you should do is to create a view on the server 2 with all those table and access THAT using the linked server. Something like this:
-- on server2 database2:
create view vwOnServer2
as
select <cols_list_here>
TABLE 4 F ON E.COL1=F.COL1--server 2 is the linked server
INNER JOIN TABLE 5 G ON F.COL1=G.COL1
LEFT JOIN TABLE 7 H ON G.COL1=F.COL1
LEFT JOIN TABLE 8 I ON I.COL1=F.COL1
LEFT JOIN -------
-------
-- on server 1 use:
Update table 1 --table 1 is on server 1 and database1
set a.col1=b.col1
from a
left join (select
C.COL1,
C.COL2,
SUM(C.COL3),
C.COL4,
C.COL5
FROM server2.database2.vwOnServer2 C
GROUP BY C.COL1,C.COL2,C.COL4,C.COL5)b
on a.col1=b.col1
a.col2=b.col2
a.col4=b.col4
a.col5=b.col5ASKER
we are not SUPPOSED TO use linked servers anywhere. I was asked to modify my SSIS package to use look up transformations. my question is how do I achieve the above update using look up transformations. Can someone please help me with that?
ASKER
what join does the look up transformation use by default?Is it inner join or left outer join?
ASKER
can someone answer my question above? I think it'd be a good idea for me to close the experts exchange account and start googling stuff myself, because I don't get apt answers right away.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@amika
You have to understand that all the experts here are volunteering. We are not paid to do this but we help people because we like to do it. We don't always meet the highest expectations from our askers but I would say that vast majority of questions are answered, maybe over 95%. Sometimes we don't have enough information to work with or we are simply mistunderstood.
Patience will overcome all these problems.
You have to understand that all the experts here are volunteering. We are not paid to do this but we help people because we like to do it. We don't always meet the highest expectations from our askers but I would say that vast majority of questions are answered, maybe over 95%. Sometimes we don't have enough information to work with or we are simply mistunderstood.
Patience will overcome all these problems.
ASKER
Well, I've posted my question on the 22nd. Today is the 29th. I've been having patience for 1 week. So, then , is having patience for a month called having patience? Well, the reason we sign up for experts-exchange is to make our job atleast a bit easier in finding things. We pay atleast $140 membership fee. If following up on a question after a week, is not considered patience, what's the point in signing up by paying the above amount. I have not posted a question a second ago and followed up with you at this second. I still do not have the highest expectations instead of paying money. Most of the time, we don't get answers faster and I try googling for stuff, most of the time. I only post questions on this site, when it is something that I'm not clear with, may be once in 3 months or probably 6 months. Most of the time, I get answers from you guys after sometime, when I figure it out in a day or two. With highly demanding jobs, it's no use, if we get answers a week after we post the questions. if we don't give points to a certain questions and we do not accept any solution after a few days, even when the solutions don't meet our expectations, then , the questions will be considered as abondaned and we are forced to accept a solution, But how about you guys taking a long time? Well, you said that you volunteer and not paid to answer these questions. You volunteer because to like it, and then, why is the delay. Most of my friends have closed their account with experts exchange for the same reason.
ASKER
Thank you! Hi! Mr.Hengel.But reading about look up, it says that the first matched row is joined between the 2 tables, but the left join only matching rows from the 2nd table and all the rows from the 2nd table. Could you please confirm if I can still use look up for my left joins?
left join/lookup : you need to be very clear about what you define to be "table 1" and "table 2" ...
consider this lookup table:
person_title:
TITLE_CODE TITLE_DESCRIPTION
MR Mister
MRS Miss
so far, it has only 2 values...
the FACT table would be a person table:
PK TITLE_CODE NAME
1 MR Hengel
2 MRS Schmit
3 DR Braun
4 MR Müller
select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
FROM person p
LEFT JOIN person_title l ON l.title_code = p.title_code
it will return 4 rows from person, even if the 3rd row does not have a matching row in the "2nd / lookup" table.
if you reverse the query:
select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
FROM person_title l
LEFT JOIN person p ON l.title_code = p.title_code
it will return only 3 records, as for the 4 row in persons (DR) there is not row in what is the the "first / fact " table.
consider this lookup table:
person_title:
TITLE_CODE TITLE_DESCRIPTION
MR Mister
MRS Miss
so far, it has only 2 values...
the FACT table would be a person table:
PK TITLE_CODE NAME
1 MR Hengel
2 MRS Schmit
3 DR Braun
4 MR Müller
select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
FROM person p
LEFT JOIN person_title l ON l.title_code = p.title_code
it will return 4 rows from person, even if the 3rd row does not have a matching row in the "2nd / lookup" table.
if you reverse the query:
select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
FROM person_title l
LEFT JOIN person p ON l.title_code = p.title_code
it will return only 3 records, as for the 4 row in persons (DR) there is not row in what is the the "first / fact " table.
ASKER
A quick question, if I have to update a column in a table on server A, by doing a left join on about 7 columns from a table on server. How can this be performed using look up transformation. Can updates be performed using look ups? Can someone please be able to provide me an example , of how this can be done using look up transformations?
Thanks a million in advance.
Thanks a million in advance.
as for the syntax of the update, check this article:
https://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html