Как найти друга базы данных

Речь сегодня пойдет об очень полезной возможности в 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

CREATE TABLE [dbo].[test_table_base_1](
        [id] [bigint] NOT NULL,
        [text] [varchar](50) NULL,
 CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED 
(
        [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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

CREATE TABLE [dbo].[test_table_base_2](
        [id] [bigint] NOT NULL,
        [text] [varchar](50) NULL,
 CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED 
(
        [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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

Код:

select * from test.dbo.test_table_base_1 – Первая база
select * from test2.dbo.test_table_base_2 –Вторая база

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

Select * from [база].[схема].[таблица]

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

Код:

select * from test.dbo.test_table_base_1 – Первая база
 union all
select * from test2.dbo.test_table_base_2 –Вторая база

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

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

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

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

Где,

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

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

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

exec sp_configure [Название параметра],[Значение]

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

exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
exec sp_configure

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

Код:

select * from  
 opendatasource ('sqlncli','Data Source=myserver;Integrated Security=SSPI')
 .test.dbo.test_table_base_1
 union all
select * from  
 opendatasource ('sqlncli','Data Source=myserver;Integrated Security=SSPI')
 .test2.dbo.test_table_base_2

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

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

Где,

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

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

select * from  
 opendatasource ('sqlncli','Data Source= myserver;user id = username; pwd=password')
 .test.dbo.test_table_base_1
 union all
select * from  
 opendatasource ('sqlncli','Data Source= myserver;user id = username; pwd=password')
 .test2.dbo.test_table_base_2

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

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

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

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:TestExcel.xls; Extended Properties=EXCEL 5.0')...[Sheet1$] ;

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

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