November 30, 2011 at 3:13 pm
I need to JOIN information from the following Linked Server Table to a Table named Mytable in a SQL Server Database named MyDatabase in the dbo Schema.
I don't remember the syntax.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00')
Any help would be greatly appreciated
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2011 at 2:30 am
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00') AS RemoteTable
INNER JOIN MyTable AS LocalTable
ON RemoteTable.SomeKeyColumn = LocalTable.SomeKeyColumn
-- Gianluca Sartori
December 2, 2011 at 6:38 pm
You may have been looking for the syntax to specify a table in a different database than where you're connected, but on the same local machine. From BOL (see http://msdn.microsoft.com/en-us/library/ms177634.aspx):
If the table or view exists in another database on the same computer that is running an instance of SQL Server, use a fully qualified name in the form database.schema.object_name. If the table or view exists outside the local server on a linked server, use a four-part name in the form linked_server.catalog.schema.object. A four-part table or view name that is constructed by using the OPENDATASOURCE function as the server part of the name can also be used to specify the table source.
Using that syntax, we can expand Gianluca's query:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00') AS RemoteTable
INNER JOIN MyDatabase.dbo.MyTable AS LocalTable
ON RemoteTable.SomeKeyColumn = LocalTable.SomeKeyColumn
BTW, I understand how you may have forgotten the syntax. It's always seemed backwards to me, having the database name first, then the "dbo".
December 2, 2011 at 7:14 pm
Thank you for your responses.:-)
I tried the join but I had to kill after 20 minutes with no result.
So I had to create dynamic SQL to include the Account Numbers in the Where Clause of the OPEN Query and it executed within 11 seconds.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 2, 2011 at 11:03 pm
Ah, yes. I've run into a similar situation using Open Query against a remote db table (not even MS SQL, but rather Netezza) that has millions of rows, from which I wanted to join to about 50,000 rows on my local db. The solution for me was to populate a table of the 50,000 keys on the remote db, then do a join there in the Open Query which then would return just the ones that were needed locally. Much faster than doing the join across a wide area network.
December 3, 2011 at 3:09 am
You might not even need a dynamic SQL.
Did you try the "EXECUTE(query with parameter) AT LinkedServer" approach? You might need to store the returned data in an indexed temp table you could then use in your join syntax.
A good explanation including samples can be found at Erland Sommarskog article.
December 3, 2011 at 10:01 am
I appreciated all of the responses.
Thank you.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2012 at 6:42 am
I have a new query where both of the tables are on an AS400.
I need to Return all Records from TableA That Have a Record in a Table called CompanyNumbers using an OpenQuery.
The Linked Server Name: LinkedServerA
WHERE (EXISTS (SELECT * FROM CompanyNumbers WHERE TableA.CompanyNumber =CompanyNumbers.CompanyNumber ))
Could someone please provide me the correct syntax?
If not an EXISTS I would even appreciate the syntax for a JOIN.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2012 at 7:10 am
In my last post I did not specify that it was a new question.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2012 at 8:07 am
I can get the following JOIN to work:
SELECT *
FROM OPENQUERY(LinkedServerA, 'SELECT * FROM TableA
INNER JOIN CompanyNumbers ON TableA.CompanyNo = CompanyNumbers.CompanyNo')
But I get a syntax error on the the WHERE EXISTS:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM TableA
WHERE EXITS (SELECT * FROM CompanyNumbers
WHERETableA.CompanyNo = CompanyNumbers.CompanyNumber))'
Any help would be appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2012 at 3:30 pm
Seems to be just a typo (EXITS instead of EXISTS):
SELECT *
FROM OPENQUERY(LSCTLRDAT1, '
SELECT *
FROM TableA
WHERE EXISTS (
SELECT *
FROM CompanyNumbers
WHERE TableA.CompanyNo = CompanyNumbers.CompanyNumber
)
')
If it still doesn't work, you could try with IN:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, '
SELECT *
FROM TableA
WHERE TableA.CompanyNo IN (
SELECT CompanyNumbers.CompanyNumber
FROM CompanyNumbers
)
')
-- Gianluca Sartori
May 4, 2012 at 3:34 pm
Dumb....
Thank you for your help!:-)
I have a situation where I need to include more than one column in the IN or Exists.
I use IN extensively but never with a comparison with more than one column.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 5, 2012 at 7:04 am
I also had the syntax wrong at the end of the statement.
I did not end the statement with )').
I don't understand the significance of the sequence of characters at the end.
It would be faster If I use a OLE DB Destination task with and query directly against DB2 so I will need to figure that out once I get done writing a rather long and boring document.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy