December 29, 2012 at 8:22 am
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.
December 29, 2012 at 8:31 am
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.
December 29, 2012 at 8:45 am
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
December 29, 2012 at 9:19 am
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.
December 29, 2012 at 10:02 am
Very grateful for your input!! thank you.
December 29, 2012 at 10:32 am
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'
December 29, 2012 at 10:36 am
i figured it out...Thank you for your assistance...
December 29, 2012 at 11:46 am
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')
December 29, 2012 at 4:10 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply