Межбазовый запрос на Transact SQL

Межбазовый запрос на Transact-SQL

Речь сегодня пойдет об очень полезной возможности в SQL это межбазовый запрос. Данный вид запроса просто незаменим, если у Вас существует несколько баз данных на одном сервере или даже на разных серверах, а так как иногда требуется получить данные сразу отовсюду, например, для отчета, то межбазовый запрос лучшее решение этой задачи.

Примечание! Сразу хочу сказать, что все примеры будем пробовать на Transact-SQL MS Sql Server 2008 в Management Studio, так как в других СУБД синтаксис будет отличаться. Также хочу заметить, что все примеры ниже требуют начальных знаний SQL, поэтому советую для начала ознакомиться с материалами: Язык запросов SQL – Оператор SELECT, Добавляем в таблицу новую колонку на SQL, Сочетание строковых функций на Transact-SQL, Transact-sql – Табличные функции и временные таблицы эти статьи помогут Вам приобрести начальные знания в SQL.

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

Межбазовый запрос

Межбазовый запрос – это запрос, который в процессе своего выполнения подключается к разным базам данных, а также в некоторых случаях к разным серверам баз данных.

А теперь давайте определимся, для каких целей могут служить межбазовые запросы, допустим, у Вас есть 3 базы данных, 2 из них расположены на одном MSSQL сервере, а одна на другом. Все они служат для какой-то определенной задачи, может быть у них даже схожая структура, но это не важно и Вам как программисту иногда требуется выгружать данные из всех баз, например, для того чтобы предоставить эти данные начальству, и Вы скорей всего запускаете запросы из каждой базы или переключаетесь из менеджера запросов на работу с той ли иной базой, но гораздо удобней было бы запустить один запрос и получить сразу все данные. Именно для этого я пользуюсь данного вида запросами, но Вы, наверное, можете найти применение и для других задач. Если Вы сталкивались с такого рода задачами, то Вам просто необходимо узнать что такое межбазовый запрос.

Примеры межбазовых запросов

И первый пример он достаточно простой, требуется тогда когда необходимо получить данные из нескольких баз расположенных на одном сервере. Для объединения этих данных будем использовать конструкцию union all, которую мы рассматривали в статье – union и union all на Transact-SQL .

И для начала, допустим, у нас есть две базы данных (test и test2), схемы dbo в которых мы создали вот такие таблицы:

Таблица в базе test

Таблица в базе test2

Я их заполнил тестовыми данными, сейчас увидите какими, и для того чтобы получить данные из этих таблиц напишем вот такой запрос:

Как видите синтаксис очень простой:

Но результата в вышеуказанном запросе будет два, и для того чтобы объединить эти запросы, используем конструкцию union all.

И результат будет уже один. И с помощью данного межбазового запроса Вы легко можете объединить данные, а еще для удобства, чтобы каждый раз не писать текст запроса можете создать представление views, для того чтобы обращаться напрямую к этому представлению.

Теперь давайте рассмотрим пример посложней, когда требуется получить данные из базы, которая располагается на другом сервере.

Для этого мы будем использовать конструкцию opendatasource.

Сразу скажу, что opendatasource работает, только если на сервере выставлен параметр Ad Hoc Distributed Queries со значением 1. Для того чтобы посмотреть этот параметр выполните процедуру sp_configure и посмотрите значение данного параметра:

• config_value — это значение которое внеслось но еще не сохранилось, т.е. сервер еще не переконфигурировался;
• run_value – текущее значение данного параметра, т.е. с которым работает сервер в данный момент.

Кстати данная процедура возвращает очень много конфигурационных параметров, которые Вы можете посмотреть.

И для того чтобы изменить данный параметр, используем туже самую процедуру, синтаксис:

А для того чтобы сконфигурировать сервер с новым значением, запустим процедуру reconfigure, и весь запрос будет выглядеть вот так:

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

Как видите результат тот же самый.

Здесь мы указали в первом параметре провайдер источника данных, т.е. SQL server (‘sqlncli’) и задали строку подключения:

• Data Source – это адрес сервера баз данных;
• Integrated Security=SSPI – при подключении использовать проверку подлинности Windows, т.е. аутентификация и авторизация пользователя будет проходить по учетным данным Windows, отлично подходит, если в сети развернута AD(Active Directory).

А если Вы хотите использовать проверку подлинности на уровне SQL сервера, то придется писать имя пользователя и пароль (которые должны быть созданы на SQL сервере) в строке подключения, например, абсолютно такой же результат, как и выше, получится, если мы напишем вот такой запрос:

Т.е. вместо параметра Integrated Security мы укажем параметры:

• user id — логин на SQL сервере;
• pwd – соответственно пароль.

Примечание! Opendatasource может подключаться и другим отличным от SQL сервера источникам для этого в параметрах указываете нужный Вам провайдер, например, для подключения к Excel документу можете использовать вот такой запрос (Синтаксис):

По межбазовым запросам все, надеюсь, данный материал был Вам интересен, и пригодится Вам на практике.

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Источник

Запрос MySQL из SQL Server с использованием связанного сервера

SQL Server имеет интересную особенность, называемую Linked Servers. Речь идет о связывании других баз данных с SQL Server и использовании их данных как локальных. Есть много мощных систем с открытым исходным кодом, написанных на PHP, и они в основном используют MySQL в качестве базы данных. В этой записи блога показано, как связать базу данных MySQL с SQL Server и как использовать связанный сервер в запросах SQL.

Что такое связанный сервер?

Связанный сервер в MSSQL – это некоторый другой сервер баз данных, подключенный к данному, что позволяет запрашивать и манипулировать данными в других базах данных. Например, мы можем связать некоторую базу данных MySQL с MSSQL и использовать ее почти как любую другую базу данных на MSSQL.

Для более детального углубления, вы можете узнать больше о том, как создать связанный сервер (Linked Server) в Microsoft SQL Server.

Хотя связь со связанными серверами осуществляется через поставщиков OLE DB, существует также поставщик OLE DB для ODBC, и мы можем использовать его, если в нашей внешней базе данных нет поставщика OLE DB.

Связанный сервер доступен для всего экземпляра SQL Server. Это означает, что все базы данных SQL Server могут использовать связанный сервер для получения данных.

Связывание MySQL с SQL Server

Добавление связанного сервера и настройка параметров соединения не всегда просты и понятны.

Чтобы связать MySQL с SQL Server, мне нужно было создать ODBC DSN для MySQL (мы назвали ее MySQLCrm). Прежде чем перейти к следующим шагам, убедитесь, что источник данных ODBC работает.

Выполните следующие шаги, чтобы связать MySQL с SQL Server:

1. Запустите SQL Server Management Studio (SSMS)
2. Подключитесь к вашему серверу
3. Разверните узел Объекты сервера из дерева слева
4. Щелкните правой кнопкой мыши на связанных серверах
5. Выберите новый связанный сервер …

Вы должны увидеть следующий диалог (или немного другой, но идея остается прежней).

NB! Обратите особое внимание на то, что вы вставляете в этот диалог. С этим набором данных мы заставили работать ссылку. Мы пробовали разные значения, и если что-то не так на один миллиметр, соединение не устанавливается. Это чертовски чувствительный диалог.

Строка подключения к базе данных MySQL должна быть такой, как показано здесь:

Также обратите внимание на OPTION = 3 – без этого мы получали только ошибки при подключении к связанному серверу.

Попробуйте сохранить, нажав OK, и посмотрите, сможете ли вы перейти на связанный сервер. Если вы получили ошибки, щелкните правой кнопкой мыши на сервере и выберите свойства. Оставив диалог открытым, перейдите на страницу параметров сервера. Установите для параметров RPC и RPC Out значение True.

Мы все еще не совсем уверены, что делают эти опции, но некоторые из тех, у кого были проблемы со связью с MySQL, заставили его работать после установки RPC в значение true.

Чтобы запрос действительно работал, нам нужно еще одно небольшое изменение, которое затрагивает весь поставщик OLE DB и, следовательно, все соединения, использующие его. Откройте узел «Поставщики» в разделе «Связанные серверы», щелкните правой кнопкой мыши MSDASQL (это поставщик OLE DB для источников данных ODBC) и выберите свойства.

Установите флажок только перед Level Zero и нажмите OK, чтобы сохранить изменения.

Запрос данных со связанного сервера

Запрашивать связанные базы данных на самом деле просто. Вот таблица клиентов из базы данных crmlinked в MySQL. Эта база данных связана с моим SQL Server.

Синтаксис запросов к связанному серверу немного отличается от того, что мы обычно пишем на SQL Server. Нам нужно использовать имена из четырех частей: server.database.schema.table. Поскольку в MySQL нет схем, а в строке подключения указано имя базы данных, мы можем их оставить, как показано здесь.

Выполнение этого запроса из SSMS дает следующий вывод. Это те же данные, что и в таблице клиентов MySQL.

Конечно, мы также можем написать более сложные запросы. Все, что ODBC может обработать, хорошо.

Смешение данных с локального и связанного сервера

Таблицы со связанного сервера не полностью изолированы от таблиц и представлений локальной базы данных. Мы также можем смешивать данные с локального и связанного сервера.

Чтобы продемонстрировать смешанный запрос к локальным и связанным таблицам, давайте напишем простой запрос, чтобы получить всех клиентов из локальной таблицы и их кредитные рейтинги из связанной таблицы.

Выполнение этого запроса дает нам следующий вывод.

Поскольку Марк отсутствует в базе данных MySQL (предположим, он новый клиент в интернет-магазине, а отдел продаж еще не имеет его в своей системе CRM), у него нет кредитного рейтинга. В текущем случае кредитные рейтинги Джона и Мэри исходят из MySQL.

Использование OPENQUERY() для выполнения запроса на связанном сервере

В приведенных выше примерах вся обработка данных выполняется на SQL Server. Это может быть очень неоптимально, если в таблицах связанных серверов много данных. Мы можем захотеть – или обычно хотим – обработать некоторые данные на связанном сервере, прежде чем SQL Server начнет локальную обработку. Для этого у нас есть OPENQUERY().

Вот пример использования функции OPENQUERY() в смешанном запросе. Мы должны указать имя связанного сервера и запрос SQL для запуска на связанном сервере при вызове OPENQUERY(). Запрос красного цвета выполняется на сервере MySQL, а результаты считываются на SQL Server для дальнейшей обработки.

OPENQUERY() – отличный способ оптимизировать и ускорить смешанные запросы, выполняя более сложные запросы к данным связанного сервера на связанном сервере.

Завершение

Связанные серверы – это мощная функция SQL Server, облегчающая нам использование данных с внешних серверов. Существует два способа написания запросов с использованием данных со связанных серверов: прямые запросы, которые выполняют всю обработку на SQL Server, и OPENQUERY(), которая позволяет нам выполнять некоторую обработку на удаленном сервере. Связанный сервер является интеграцией, поэтому его использование требует особой осторожности. Планирование и измерение производительности должны быть обязательными при планировании использования связанного сервера.

Источник



Запрос таблицы с другого сервера

Как реализовать такую задачу, может кто сталкивался.

Был при экспериментах составлен запрос на обновление записей:

Работает! Сравниваются таблицы [Table] и [Table_Copy]. При отсутствии записей в [Table_Copy], они дополняются из [Table]. Но [Table] и [Table_Copy] они на одной машине..
А вот если [Table] и [Table_Copy] на разных машинах как быть?

Сообщение было отредактировано: 16 мар 15, 10:18

Один из вариантов
1)Результаты сохранять как текстовый файл.
2)Bulk Insert со всякими анализами и обновлениями.

RTFM Linked server

Сообщение было отредактировано: 16 мар 15, 10:19

а запрос к 1 полю [ID]:
SELECT [VM-FREDDY\SQLEXPRESS].[work].[dbo].[Kontr_Users].[ID]
FROM [VM-FREDDY\SQLEXPRESS].[work].[dbo].[Kontr_Users]
не работает Вываливает ошибку в окне создания запросов
«The multi-part identifier «VM-FREDDY\SQLEXPRESS.work.dbo.Kontr_Users.ID» could not be bound.»

MERGE INTO [Kontr_Users] USING [Users] ON (Users.ID = Kontr_Users.ID)
WHEN NOT MATCHED THEN
INSERT ([ID],[Num]) VALUES ([Users].[ID],[Users].[Num]) ; — который работает с таблицей [Kontr_Users] находящейся на локальном сервере

«The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.»

INSERT [VM-FREDDY\SQLEXPRESS].[work].[dbo].[Kontr_Users] AS t
SELECT * FROM [Users] WHERE [Users].[ID]<>t.[ID]

но ругается
«Incorrect syntax near the keyword ‘AS’.»

если вставка из другой таблицы
INSERT [имя таблицы] SELECT * FROM [имя др.таблицы]

Я просто во втором случае пытаюсь вставить алиас. [имя таблицы] as t
и потом при выборке из другой таблицы поставить условие выборки,
чтобы вставлялись записи в [имя таблицы] которых нет в [имя др.таблицы] (WHERE [имя др.таблицы].[ID] <> [имя таблицы].[ID])

или что то не так понимаю. Почему на объявление алиаса ругается (или его нельзя объявлять в этом месте)?

Я не совсем понимаю ваш запрос — для меня сложный — но он у меня выдал ошибку
«Column name or number of supplied values does not match table definition.» — хотя по структуре обе таблицы одинаковы,

Да, там про алиас ни слова. Я видел.
Но я думал, что алиас можно объявлять для упрощения восприятия запроса.
Итиого — что алиас в иструкции INSERT объявлять нельзя?

Но если не через него:
INSERT [VM-FREDDY\SQLEXPRESS].[work].[dbo].[Kontr_Users]
SELECT * FROM [Users] WHERE [Users].ID <> [VM-FREDDY\SQLEXPRESS].[work].[dbo].[Kontr_Users].ID

Источник

Как я могу присоединиться к двум таблицам из двух разных подключений или экземпляров в SQL Server? [Дубликат]

В ES6 вы можете использовать деструктурирование массива, чтобы поймать ваши группы:

• первая запятая в последней пусть пропускает первое значение результирующего массива, который представляет собой целую согласованную строку
• the || [] после .match () предотвратит ошибку разрушения, если совпадений нет (поскольку .match () вернет null ])

13 ответов

, поэтому он должен выглядеть следующим образом:

Объекты сервера —> связанный сервер —> новый связанный сервер

В связанном сервере введите имя сервера или IP-адрес для другого сервера и выберите SQL Server In Security (сделайте это с помощью этой безопасности контекст) Напишите логин и пароль для другого сервера

Теперь подключитесь, затем используйте

У меня была такая же проблема для подключения SQL_server 2008 к SQL_server 2016, размещенного на удаленном сервере. Другие ответы не помогли мне прямолинейно.

расширенный ответ для удаленных IP-соединений db:

Шаг 1: серверы ссылок

. где SRV_NAME является придуманным именем. Мы будем использовать его для обращения к удаленному серверу из наших запросов. aaa.bbb.ccc.ddd — это IP-адрес удаленного сервера, на котором размещен ваш SQLserver DB.

Шаг 2. Запустите ваши запросы. Например:

Я знаю, что это старый вопрос, но я использую синонимы. Предположительно запрос выполняется в сервере базы данных A и ищет таблицу на сервере базы данных B, которая не существует на сервере A. Затем добавьте синоним в базу данных A, которая вызывает вашу таблицу с сервера B. В вашем запросе не требуется включая любые схемы или разные имена баз данных, просто вызовите имя таблицы в обычном режиме, и оно будет работать.

Нет необходимости связывать серверы, так как синонимы для одного говорят, являются своего рода привязкой.

Это все прекрасные ответы, но этого не хватает, и у него есть свои мощные способы использования. Возможно, это не подходит тем, что хотел OP, но вопрос был расплывчатым, и я чувствую, что другие могут найти здесь свой путь. В основном вы можете использовать 1 окно для запуска запроса на нескольких серверах, вот как это сделать:

В SSMS открыть зарегистрированные серверы и создать новую группу серверов в группах локальных серверов.

В этой группе создайте новую регистрацию сервера для каждого сервера, который вы хотите запросить. Если имена БД разные, установите для каждого по умолчанию значение по умолчанию.

Теперь вернитесь к группе, созданной на первом шаге, щелкните правой кнопкой мыши и выберите «Новый запрос». Откроется новое окно запроса, и любой запрошенный вами запрос будет выполнен на каждом сервере в группе. Результаты представлены в одном наборе данных с дополнительным именем столбца, указывающим, с какого сервера произошла запись. Если вы используете строку состояния, вы заметите, что имя сервера заменено несколькими.

Источник