I've written a lot of SQL over the years, but I recently ran into a situation where I wanted to select records from a SQL table based on values in a table which is local to Access. I can simply create the query in Access joining the linked table to the Access table, but I'm wondering if there isn't a more efficient method. This stemmed from an EE question in which the user indicated that they have read-only permissions on the SQL Server
This is for a report, so the recordset returned from SQL Server does not have to be updateable. With the help of a couple of Access MVPs, I found several syntax which worked in a pass-through query:
SET NOCOUNT ON
CREATE TABLE #TempData (PO_Number nvarchar(10))
Insert into #TempData values ('abc123'), ('xyz456'), ('mno789')
FROM yourTable INNER JOIN #TempData ON yourTable.FieldName = #TempDate.PO_Number
However, I doubt this syntax would work in a read-only connection to the server.
SET NOCOUNT ON
declare @TempData as table (PO_Number nvarchar(10))
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')
FROM yourTable INNER JOIN @TempData ON yourTable.FieldName = @TempDate.PO_Number
and one of the MVPs' recommended a syntax of:
(SELECT 'abc123' PO_Num
UNION ALL SELECT 'xyz456'
UNION ALL SELECT 'mno789'
FROM SomeTable INNER JOIN cteC ON SomeTable.PO_Number = cteC.PO_Num
So, what are the advantages/disadvantages of each of these?
Do any of these techniques allow you to create an index on the new table (temp, cte, or variable)?
If the tempdb method will not work with a read-only connection, can you modify the syntax of that example so that it would run with a read-only connection? Maybe with a Run As command?
I don't know what version SQL Server the OP is using, so can someone tell me what versions of SQL Server that the CTE and table variables became available.
Thanks for your assistance.