Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
Уважаемые специалисты, я столкнулся со следующей задачей:
Есть больше 1000 одинаковых по стуктуре таблиц с разными именами (меняется дата создания таблицы).
Структура таблиц следующая:
Id <int>
ClientName <varchar(50)>
CreateDate <datetime>
OperationType <int>

Имя таблиц выглядит так:

OperationsTable_2010_01_01
OperationsTable_2010_01_02
OperationsTable_2010_01_03
......
OperationsTable_2015_12_14

Каждая таблица создается в 00:00:00:275 каждого дня

Необходимо выбрать данные за определенный период, например:
With alltable as
(Select * from OperationsTable_2010_01_01
Union all
Select * from OperationsTable_2010_01_02
Union all
...
Select * from OperationsTable_2010_01_20
)
Select * from alltable
Where operationType =10

Так можно выбрать небольшое количество таблиц с заранее известным диапазоном дат, но если диапазон постоянно меняется, то необходимо как-то выбирать таблицы в зависимости от этого диапазона, наприер используя таблицу sysobject и ее поле crdate (дата создания таблицы), тогда я могу получить имена таблиц, созданные за опредленный период

Select name
From sysobjects
Where crdate between '2015-05-11' and '2015-07-03'

Так я получаю список таблиц за определнный период
Но как мне выбрать в одну результирующую таблицу (можно временную) данные из полученных таблиц.
Помогите пожалуйста
14 дек 15, 22:53    [18559995]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
krolig
Member [заблокирован]

Откуда: Владимир
Сообщений: 9
Александр Сипачёв,

почему бы не сделать INSERT INTO новая_суммарная_таблица VALUES (куча ваших union-select-ов )?
14 дек 15, 22:58    [18560022]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
krolig,
Динамический sql + курсор или склейка
14 дек 15, 23:28    [18560163]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
krolig
почему бы не сделать INSERT INTO новая_суммарная_таблица VALUES (куча ваших union-select-ов )?

Не надо советовать синтаксис, который вы сами только что придумали
15 дек 15, 09:30    [18560974]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1451
Александр Сипачёв,

если вам необходимо разово выбрать данные то неплохо посоветовали (хоть и с кривым синтаксисом) слить все в одну результирующую таблицу аля:

declare @exe nvarchar(max)
set @exe = N'select 0 as id, "" as clientName, "19000101" as CreateDate, 0 as OperationType '
select @exe = @exe + CHAR(13) + CHAR(10)+
              'union all select id, CreateDate, OperationType from ' + OBJECT_NAME(s.id) + ' '
from sysobjects s
where s.type = 'U'
  and s.name like 'OperationsTable%'
  and s.crdate between '20150305' and '20151216'

set @exe = REPLACE(@exe, char(34), char(39))
print @exe
- после чего вывод принта исполнить как готовый t-sql код (дописал необходимый инсерт или что там вам нужно)

если необходимо динамически исполнять в зависимости от входных параметров, вместо print @exe использовать exec (@exe), предварительно допилив логику думаю сами додумаетесь как это реализовать под ваши нужды
15 дек 15, 09:53    [18561050]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
felix_ff,
Спасибо, сейчас попробую разобраться.
Мне этот код нужно будет выполнять постоянно, с разными входными параметрами.
15 дек 15, 10:55    [18561370]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
Александр Сипачёв
Мне этот код нужно будет выполнять постоянно, с разными входными параметрами.

Сделайте постоянную таблицу, куда и заносите все данные.
15 дек 15, 10:58    [18561384]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
И год назад, кстати, вы уже задавали подобный вопрос
15 дек 15, 10:59    [18561390]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
felix_ff, разовью тему.

Александр Сипачёв, я правильно понимаю, что у вас в каждой таблице хранятся данные исключительно за день, указанный в названии таблицы?

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

Скрипт на пересоздание вьюхи (первоначально ее можно создать с любым определением) будет примерно такой:

declare @sql nvarchar(max);

select @sql = N'alter view OperationsTable
as'

select @sql +=
		CHAR(13) + CHAR(10)+ N'select Id, ClientName, CreateDate, OperationType from ' + 
		t.Name + N' where CreateDate >= ''' + convert(nvarchar(8), t.nameToDate, 112) +
		N''' and CreateDate < ''' + convert(nvarchar(8), dateadd(day, 1, t.nameToDate), 112) + N'''' + CHAR(13) + CHAR(10) +
		N'union all'
from (	select	Table_name as Name
			,	cast(substring(t.TABLE_NAME, 17, 4) + substring(t.TABLE_NAME, 22, 2)  + substring(t.TABLE_NAME, 25, 2) as date) as nameToDate 
		from	INFORMATION_SCHEMA.TABLES t
		where	t.TABLE_TYPE = 'BASE TABLE'
			and t.TABLE_NAME like 'OperationsTable[_]____[_]__[_]__') as t

set @sql = left(@sql, len(@sql) - 11);

exec(@sql)
15 дек 15, 11:26    [18561566]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Minamoto
у вас в каждой таблице хранятся данные исключительно за день, указанный в названии таблицы
Это самое абсурдное решение, которое только можно себе представить.
Которое совсем не ложится на концепцию реляционной базы данных.
Структура БД должна меняться только в исключительных случаях.
Основные манипуляции должны происходить с хранящимися данными.
15 дек 15, 11:31    [18561611]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
Minamoto,

Job не подойдет, так как мне не известны параметры запроса.
В итоге у меня будет отчет, который нужно будет сформировать за 2 дня Марта или за 5 дней Мая или с 1 по 10 декабря.
15 дек 15, 11:34    [18561634]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
iap,

Полностью с Вами согласен, но не я разработчик базы данных, я только пытаюсь получать из нее сведения.
Один день - примерно 25 000 000 записей
15 дек 15, 11:37    [18561660]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
iap
Minamoto
у вас в каждой таблице хранятся данные исключительно за день, указанный в названии таблицы
Это самое абсурдное решение, которое только можно себе представить.
Которое совсем не ложится на концепцию реляционной базы данных.
Структура БД должна меняться только в исключительных случаях.
Основные манипуляции должны происходить с хранящимися данными.

Вы, может, и в секционирование не верите?
И способов секционирования на версиях сервера, не поддерживающих секционирование по умолчанию, не знаете?
15 дек 15, 11:37    [18561661]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Александр Сипачёв
Minamoto,

Job не подойдет, так как мне не известны параметры запроса.
В итоге у меня будет отчет, который нужно будет сформировать за 2 дня Марта или за 5 дней Мая или с 1 по 10 декабря.

Вы не поняли идею. Джоб будет создавать представление, содержащее данные всех таблиц.
В отчете обращаетесь к представлению, при построении запроса выборки будут делаться из нужных таблиц автоматически.
15 дек 15, 11:39    [18561672]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
Minamoto
felix_ff, разовью тему.

Александр Сипачёв, я правильно понимаю, что у вас в каждой таблице хранятся данные исключительно за день, указанный в названии таблицы?

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

Скрипт на пересоздание вьюхи (первоначально ее можно создать с любым определением) будет примерно такой:

declare @sql nvarchar(max);

select @sql = N'alter view OperationsTable
as'

select @sql +=
		CHAR(13) + CHAR(10)+ N'select Id, ClientName, CreateDate, OperationType from ' + 
		t.Name + N' where CreateDate >= ''' + convert(nvarchar(8), t.nameToDate, 112) +
		N''' and CreateDate < ''' + convert(nvarchar(8), dateadd(day, 1, t.nameToDate), 112) + N'''' + CHAR(13) + CHAR(10) +
		N'union all'
from (	select	Table_name as Name
			,	cast(substring(t.TABLE_NAME, 17, 4) + substring(t.TABLE_NAME, 22, 2)  + substring(t.TABLE_NAME, 25, 2) as date) as nameToDate 
		from	INFORMATION_SCHEMA.TABLES t
		where	t.TABLE_TYPE = 'BASE TABLE'
			and t.TABLE_NAME like 'OperationsTable[_]____[_]__[_]__') as t

set @sql = left(@sql, len(@sql) - 11);

exec(@sql)

ДОШЛО, СПАСИБО!
Способ вроде работает.
Есть еще одна "загвоздка", если в UNION ALL выбирается более 255 таблиц, то выдается ошибка: Too many tabke names in the query. The maximum allowable is 256. Это если исходные с 1 января по 12 декабря
15 дек 15, 11:54    [18561759]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Александр Сипачёв, я думал о том, что ограничение будет, не знал конкретного значения.

Можно сделать такую конструкцию - создавать вьюхи для каждого месяца, для каждого года (с объединением вьюх по месяцам) и общую - с объединением вьюх по годам. Во всех, естественно, также нужно сделать условие на даты, чтобы ненароком к сканированию всех таблиц в базе не придти.

В общем, идея у вас есть, развивайте дальше уже самостоятельно.
15 дек 15, 12:00    [18561796]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
Можно сделать такую конструкцию - создавать вьюхи для каждого месяца, для каждого года (с объединением вьюх по месяцам) и общую - с объединением вьюх по годам. Во всех, естественно, также нужно сделать условие на даты, чтобы ненароком к сканированию всех таблиц в базе не придти.

На всякий случай - это только идея, возможно ли ее реализовать - я не знаю, возможно, ограничение на количество таблиц сработает и тут. В этом случае других вариантов, кроме как сливать все данные в более крупные таблицы, я не вижу.
15 дек 15, 12:02    [18561815]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Александр Сипачёв
Member

Откуда:
Сообщений: 22
СПАСИБО, пробую идею с объединением Представлений (Вьюх)
15 дек 15, 12:09    [18561860]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
Александр Сипачёв
пробую идею с объединением Представлений (Вьюх)

Вы думаете, что это позволит обойти "Too many tabke names in the query. The maximum allowable is 256. " ?
15 дек 15, 12:10    [18561865]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать данные из 1000 и более одинаковых по структуре таблиц  [new]
mishanya3624
Member

Откуда:
Сообщений: 795
Glory
Александр Сипачёв
пробую идею с объединением Представлений (Вьюх)

Вы думаете, что это позволит обойти "Too many tabke names in the query. The maximum allowable is 256. " ?

во во, тоже интересно.
15 дек 15, 12:11    [18561871]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить