Как OPENQUERY отличается для SELECT и INSERT?

Я знаю, что следующий запрос вытащит результирующий набор из связанного сервера:

SELECT * FROM openquery(DEVMYSQL, 
    'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

Однако, это тот же самый случай, когда речь идет о вставке? Потянет ли он результирующий набор или он просто получит информацию о столбце?

INSERT INTO openquery(DEVMYSQL, 
     'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

Если первое, то это очень неэффективно. Должен ли я ограничить возвращаемый результат, и это повлияет на мой INSERT?

Это в основном вопрос о том, как OPENQUERY работает, когда дело доходит до SELECT и INSERT.

Я ценю любую помощь.

Ответ 1

Не уверен, что вы пытаетесь выполнить с помощью INSERT.

Правильный синтаксис (если вы хотите вставить на REMOTE-сервер) должен быть

INSERT into openquery(MyServer, 'dbo.event_cast') values ('','')

Выбор только задерживает вставку, получая то, что когда-либо возвращает запрос select (безрезультатно), не давая вам дополнительной информации. Кроме того, с помощью openquery вы можете использовать этот синтаксис, вернее, для вставки:

INSERT into myserver.mydatabase.dbo.event_Cast values('','')

Но, если вы пытаетесь вставить в LOCAL сервер, значения, полученные при выборе синтаксиса, должны быть:

INSERT into dbo.my_localtable SELECT * FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast')

И да, предложение будет вставлять значения, а не только информацию о столбцах.

Если вы хотите реплицировать таблицу локально простым

SELECT top 1 * into new_local_event_cast FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast');
TRUNCATE TABLE new_local_event_cast;

будет достаточно

Ответ 2

Если SELECT будет возвращать записи, INSERT не будет возвращать результирующий набор, за исключением случаев, когда это повлияло на количество записей. Это можно подавить, используя SET NOCOUNT ON; однако я не уверен, что подавление будет относиться к видимости или количеству строк, которые действительно наступают.

    INSERT INTO OPENQUERY(MYSERVER, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [Blah].[dbo].[SQL_Drives]')
    SELECT 'X', 2, 'MyServer'

(1 row(s) affected)

Что касается записей, возвращаемых из INSERT, единственный способ сделать это - использовать предложение OUTPUT. Клиентская машина не будет иметь доступ к строкам OUTPUT INSERTED, чтобы они не могли быть возвращены. Если вы попытаетесь запустить следующее, вы получите сообщение об ошибке:

INSERT INTO OPENQUERY(MYSERVER, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [BLAH].[dbo].[SQL_Drives]')
OUTPUT INSERTED.*
SELECT 'X', 2, 'MyServer'

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.


 -- EDIT RESULTS OF PROFILER ---------------------------      


-- Statements that occured on server called through OPENQUERY
    exec sp_cursoropen @p1 output,N'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [MyServer].[dbo].[SQL_Drives]',@p3 output,@p4 output,@p5 output
    select @p1, @p3, @p4, @p5

    exec sp_cursor 180150009,4,0,N'[MyServer].[dbo].[SQL_Drives]',@Drive_Letter='X',@MBFree=2,@Server='MyServer'


--Statements that occured on client
    INSERT INTO OPENQUERY(MyServer, 'SELECT [Drive_Letter] ,[MBFree],[Server] FROM [MyServer].[dbo].[SQL_Drives]')
    SELECT 'X', 2, 'MyServer'

Ответ 3

Когда вы используете OPENQUERY для выполнения INSERT, он сначала выполнит SELECT и выбросит результаты. Он использует метаданные столбца из набора результатов, чтобы определить, как отправить команду INSERT. По этой причине вы всегда должны добавить where 1=0 в конец SELECT.

От SQL Server до MySQL правильный синтаксис:

insert into openquery(MYSQLDEV, 'select * from insert_test where 1=0') values ('test');

Если вы активируете общий журнал запросов на стороне MySQL, вы увидите эти команды из SQL Server:

SET NAMES utf8
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
set @@sql_select_limit=1
select * from insert_test where 1=0
select * from insert_test where 1=0
INSERT INTO `database`.`insert_test`(`column`) VALUES ('test')

Итак, вы можете видеть, что выбор выполняется дважды. Без where 1=0 все строки будут возвращены сервером MySQL.