Когда в качестве запроса связанному серверу передается обычный SELECT без каких-либо условий, то проблем не возникает. Правда редко когда удается обойтись обычным селектом, особенно когда в таблице на другой стороне лежат милиионы записей, необходимость иметь на своей стороне такой объем информации отсутствует, а взаимодействие серверов сильно ограничено шириной канала в интернет.

Понятно, что в данном случае чистый SELECT – не вариант, нужно ограничивать запрос с помощью WHERE. Вот только механизм работы linked серверов устроен так, что связанный сервер сначала возвращает весь объем данных на сервер с которого пришел запрос, а уж потом сам MS SQL применяет к массиву данных условие WHERE.

Для того чтобы обойти это ограничение необходимо использовать OPENQUERY. Запрос, завернутый в эту функцию, связанный сервер обработает на своей стороне и вернет готовый результат.

Приятно и то, что результат работы OPENQUERY можно использовать в качестве исходной таблицы в INSERT, UPDATE и DELETE запросах, и, разумеется, в обычном SELECT’e.

В качестве аргументов функции передаются: имя связанного сервера и текст запроса.

В общем виде OPENQUERY можно применить следующим образом:

SELECT * FROM OPENQUERY(linked_server, 
'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db 
WHERE remote_table.column_N IN (''value_1'',''value_2'')')

В связи с тем, что сам текст SQL-запроса в OPENQUERY должен быть окружен апострофами, то, для того, чтобы параметры для поиска были правильно обработаны, их нужно заключать в двойные апострофы.

OPENQUERY и динамические SQL-запросы

MSDN честно предупреждает, что в качестве аргументов OPENQUERY нельзя применять параметры. Поэтому, если применять OPENQUERY так, как показано в примере выше, о динамическом SQL можно забыть.

Чтобы обойти это ограничение, приведенный выше запрос можно завернуть в апострофы, затолкать его в переменную, а потом подсунуть эту переменную функции EXEC.

Предположим, что в условии WHERE, я хочу применить некий набор значений, который можно получить селектом из таблицы, потом затолкать эти значения в OPENQUERY и выполнить сформированный запрос на связанном сервере, а полученный результат записать в таблицу.

DECLARE @values varchar(MAX)
DECLARE @sql varchar (MAX)

SET @values = (SELECT [Column1] + ',' FROM [DB].[Table1]
GROUP BY [Column1] FOR XML PATH(''))
SET @values = REPLACE(@values,',',''',''')

SET @sql = 'SELECT remote_table.column_1, remote_table.column_2
FROM remote_db WHERE remote_table.column_N IN (''' + @values + ''')'

SET @sql = N'INSERT INTO [Table2] SELECT *
FROM OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')'

EXEC (@sql)

Обратить внимание следует на следующие моменты: во-первых,  в параметр @values, имеющий тип varchar, нельзя передать несколько значение из таблицы, так как это строковый параметр. Поэтому приходится объединять все значения из таблицы в одну строку, разделяя их запятыми. Для этого вполне можно применить XML PATH.

Во-вторых, главное не косячить с апострофами. Поэтому для начала я обрабатываю строку @values, проходясь по ней REPLACE’ ом, а после того, как будет сформирован запрос к связанному серверу, прохожусь по нему REPLACE’ом еще раз, экранируя апострофы, чтобы гамотно завернуть всю конструкцию в OPENQUERY.

Конечный SQL-запрос хранится в параметре @sql, который и передается функции EXEC.

Ограничение на длину запроса

К сожалению, когда в качестве значений для условия WHERE используется SELECT из таблицы, всегда есть вероятность получить довольно большо количество данных. В то же время, максимальная длина параметра @sql не может превышать 8000 символов. Поэтому при неудачном стечении обстоятельств в функцию EXEC будет передан обрезанный SQL запрос, который вернет ошибку.

Я решил бороться с этим, отправляя на связанный сервер запрос, содержащий максимум 250 значений в условии WHERE за раз.

DECLARE @values varchar(MAX)
DECLARE @sql varchar (MAX)
DECLARE @counter numeric(6,2);

SET @counter = (SELECT COUNT([Column1]) FROM [DB].[Table1] GROUP BY [Column1])
-- Так как количество значений, передаваемых за раз условию WHERE
-- 250, то количество полученных рядов нужно разделить на размер пакета.
-- Если рядов будет меньше 250, то достаточно одной итерации, поэтому
-- счетчик устанавливается в значение 1
SET @counter = CEILING(@counter/250)

WHILE @counter <> 0
BEGIN
-- Так как в каждой итерации данные записываются в результирующую таблицу, то
-- можно построить запрос для формирования DynamicTable таким образом, чтобы в нее
-- возвращались только те значения, для которых еще не загружены данные.

WITH DynamicTable AS
	(SELECT A.[Column1] FROM [DB].[Table1] AS A WHERE NOT EXISTS
(SELECT B.[Column1] FROM [DB].[Table2] AS B
WHERE B.[Column1] = A.[Column1])
GROUP BY [Column1])
SELECT @values = (SELECT TOP 250 [Column1] + ',' FROM DynamicTable FOR XML PATH(''))
SET @values = REPLACE(@values,',',''',''')

SET @counter = @counter - 1

SET @sql = 'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db
WHERE remote_table.column_N IN (''' + @values + ''')'

SET @sql = N'INSERT INTO [Table2] SELECT * FROM
OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')'

EXEC (@sql)

END

Ссылки:

OPENQUERY на MSDN: http://msdn.microsoft.com/ru-ru/library/ms188427.aspx
Динамический SQL запросы (Английский): http://www.sommarskog.se/dynamic_sql.htm