Geek asks and answers

Two requests. The same way out. One takes 2 hours and the remaining 0 seconds. What for?

I have several identifiers inserted in a temporary table #Aas follows:

SELECT DISTINCT ID
INTO #A
FROM LocalDB.dbo.LocalTable1
WHERE ID NOT IN (SELECT DISTINCT ID FROM LocalDB.dbo.LocalTable2)
GO

CREATE INDEX TT ON #A(ID)
GO

I am trying to get some information from a remote linked server using the identifiers that I collected in the previous step:

Request 1:

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
WHERE ID IN (SELECT ID FROM #A)

Request 2:

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
INNER JOIN #A Y
ON X.ID = Y.ID

Now in the next query, I get the temp table output, copy the rows and format them correctly in a comma-separated list, and manually put it in the query.

Request 3:

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
WHERE ID IN (-- Put all identifiers here --)

1 2 2 , 3 0 ( 200 ). , , , , , , , , , - .

, , , ?

+5
2

1 2 , . RAM, , , , .

3 , .

, , . 1/2 , ; 3 .

, , , ..

- , , . , , .

, ( ), .

+10

, , @ChrisLively:

SELECT DISTINCT ID
INTO #A
FROM LocalDB.dbo.LocalTable1
WHERE ID NOT IN (SELECT DISTINCT ID FROM LocalDB.dbo.LocalTable2)
GO

CREATE INDEX TT ON #A(ID)
GO

DECLARE @IDList VARCHAR(MAX)
SELECT @IDList=(SELECT TOP 1 REPLACE(RTRIM((
                SELECT DISTINCT CAST(ID AS VARCHAR(MAX)) + ' ' 
                FROM #A AS InnerTable               
                FOR XML PATH (''))),' ',', '))
FROM #A AS OuterResults


DECLARE @sql AS varchar(MAX)
SET @sql = 'SELECT * FROM RemoteLinkedServer.RemoteDB.dbo.RemoteTable X WHERE ID IN (' + @IDList + ')'

EXEC (@sql)

DROP TABLE #A
GO
+3

More articles:


All Articles