SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


joining to sql during openquery call to MySql


joining to sql during openquery call to MySql

Author
Message
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
i am querying a MySql view for data, but need to join to a sql table for three additional fields.

SET IDENTITY_INSERT CALLSNEW ON
INSERT INTO [cas].[dbo].[CALLSNEW]
([calls_key]
,[ext])

SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls')
Where calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')


but i need to join to a linked SQL server as well to get a few more fields. I could use suggestions on how to do that.

SELECT dept = dbo.EMP.dept, fname = dbo.EMP.fname, lname = dbo.EMP.lname, empnum = dbo.EMP.empnum
FROM linkedserver.dbo.EMP
WHERE dbo.EMP.ext = ext
AND dbo.EMP.active = 1



thanks for your time.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39061 Visits: 38518
TryingToLearn (12/29/2012)
i am querying a MySql view for data, but need to join to a sql table for three additional fields.

SET IDENTITY_INSERT CALLSNEW ON
INSERT INTO [cas].[dbo].[CALLSNEW]
([calls_key]
,[ext])

SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls')
Where calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')


but i need to join to a linked SQL server as well to get a few more fields. I could use suggestions on how to do that.

SELECT dept = dbo.EMP.dept, fname = dbo.EMP.fname, lname = dbo.EMP.lname, empnum = dbo.EMP.empnum
FROM linkedserver.dbo.EMP
WHERE dbo.EMP.ext = ext
AND dbo.EMP.active = 1



thanks for your time.


Need more information. From just what you have posted, not sure how these two tables are related and could be joned together.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
Thanks for replying.

i am pulling ext from the MySQL view which in also in the EMP SQL table.

Based on the EXT i need to get dept, fname, lname, empum from thenSQL table
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39061 Visits: 38518
I would say something like this should work. I don't have a linked server of any kind on my system so I can't test it:



SELECT
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum ,
calls_key = oq.calls_key,
ext = oq.ext
FROM
linkedserver.dbo.EMP e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')





Also, please note the use of the table aliases especially on the linked server. The use of 3 and 4 part names in the select list is a deprecated feature and should not be used. You should use table alaises in the from clause.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
Very grateful for your input!! thank you.
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
Sorry, The EMP table is not on a linked server, it is a SQL table in the Local database..

does that explain the error ' ambiguous column name 'ext'
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
i figured it out...Thank you for your assistance...
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
I now need to turn this into a insert statement, calls_key a the primary key and i'm getting a new error on that issue. Can you help on that?

Thank you

SET IDENTITY_INSERT CALLSNEW ON
--INSERT INTO [CALLSNEW]
INSERT INTO [cas].[dbo].[CALLSNEW]

SELECT
calls_key = oq.calls_key,
ext = oq.ext,
calltype = oq.calltype,
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum
FROM
[EMP] e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext, calltype FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = oq.ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-group')
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39061 Visits: 38518
TryingToLearn (12/29/2012)
I now need to turn this into a insert statement, calls_key a the primary key and i'm getting a new error on that issue. Can you help on that?

Thank you

SET IDENTITY_INSERT CALLSNEW ON
--INSERT INTO [CALLSNEW]
INSERT INTO [cas].[dbo].[CALLSNEW]

SELECT
calls_key = oq.calls_key,
ext = oq.ext,
calltype = oq.calltype,
dept = e.dept,
fname = e.fname,
lname = e.lname,
empnum = e.empnum
FROM
[EMP] e
inner join OPENQUERY(PHONEDB,'SELECT calls_key, ext, calltype FROM calls') oq
on (e.ext = oq.ext)
WHERE
e.ext = oq.ext
AND e.active = 1
and oq.calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-group')


Nope, I have no idea what the problem is with your code. My crystal ball has been in the shop for years, and I don't expect to get it back anytime soon. Also, the force may be strong in this one, but it isn't that strong.

What all the sarcasm is saying is that you haven't provided enough information for anyone not looking over your shoulder to be able to help you.

Please read the first article I reference below in my signature block. It will walk you through everything you need to post to get the best possible answers to your questions.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search