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

Откуда:
Сообщений: 6
Доброго времени суток. Помогите направить на путь истинный.
Сразу - я не программист, но судьба заставила написать выражение.
Имеется инстанс MS SQL с некоторым количеством баз, содержащих одинаковые таблицы для выборки.
Требуется обойти все базы по маске имени, сделать запрос и вставить значения во временную таблицу.

Сам код:
USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.#otchet') IS NOT NULL
        DROP TABLE #otchet
       GO
       CREATE TABLE #otchet
        (
       	BASE_NAME VARCHAR(60),
       	Raspisanie INT
       )
        
DECLARE @DBName VARCHAR(60),
        @script VARCHAR(MAX)
DECLARE dbs CURSOR LOCAL FORWARD_ONLY FOR
SELECT
  name
FROM sys.databases
WHERE name LIKE 'EMISZ\_KO\_%' ESCAPE '\'
OPEN dbs
FETCH NEXT FROM dbs INTO @DBName
WHILE @@fetch_status = 0
BEGIN
  PRINT '----------Выбрал базу' + CAST(@DBName AS VARCHAR(60))
  SET @script = 'Use [' + @DBName + '] Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + 'dbo.PLANNING WHERE DATE_CONS >= ''01-07-2014'' AND DATE_CONS <= ''31-07-2014'') 
  INSERT INTO #otchet (BASE_NAME, Raspisanie) VALUES (' + @DBName + ', @RASPISANIE)'
  EXEC (@script)
  FETCH NEXT FROM dbs INTO @DBName
END
CLOSE dbs
DEALLOCATE dbs
SELECT  * FROM #otchet


Сообщения при выполнении:
------ Выполнение начато: отчет.sql ------
Контекст базы данных изменен на "tempdb".
Выполнение завершено успешно [0,003c]
Выполнение завершено успешно [0,001c]
----------Выбрал базу EMISZ_KO_KGKP
Ошибка: (8,1): Недопустимое имя столбца "EMISZ_KO_KGKP".
----------Выбрал базу EMISZ_KO_KG_D
Ошибка: (8,1): Недопустимое имя столбца "EMISZ_KO_KG_D".
----------Выбрал базу EMISZ_KO_KODB
Ошибка: (8,1): Недопустимое имя столбца "EMISZ_KO_KODB".
....... и т.д.

Соответственно, селект возвращает пустую таблицу #otchet.

Выражение (SELECT COUNT (*) FROM EMISZ_KO_KGKP.dbo.PLANNING WHERE DATE_CONS >= '01-07-2014' AND DATE_CONS <= '31-07-2014') возвращает значение, например 9734.

Понимаю, что накосячил где-то с использованием переменных в exec (или с расстановкой ' ' ').

Надеюсь на помощь.
6 ноя 14, 19:33    [16808133]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
romich7,

  SET @script = 'Use [' + @DBName + '] Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + 'dbo.PLANNING WHERE DATE_CONS >= ''01-07-2014'' AND DATE_CONS <= ''31-07-2014'') 
  INSERT INTO #otchet (BASE_NAME, Raspisanie) VALUES (''' + @DBName + ''', @RASPISANIE)'
6 ноя 14, 20:01    [16808214]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
romich7
Member

Откуда:
Сообщений: 6
Mind,
Преогромнейшее спасибо! Отработал!

дополнительно исправил еще один свой косяк:
SET @script = 'Use [' + @DBName + '] Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + '.dbo.PLANNING WHERE ' + @DBName + '.dbo.PLANNING.DATE_CONS >= ''01-07-2014'' AND ' + @DBName + '.dbo.PLANNING.DATE_CONS <= ''31-07-2014'')
  INSERT INTO #otchet (BASE_NAME, Raspisanie) VALUES (''' + @DBName + ''', @RASPISANIE)'


Добавил . перед dbo;
' + @DBName + '.dbo.PLANNING. перед date_cons

Большое благодарю!
Во временной таблице то, что нужно.
6 ноя 14, 20:27    [16808310]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
romich7
Member

Откуда:
Сообщений: 6
Далее, буду использовать в exec несколько выборок и пытаюсь (усложниться/облегчить) текст скрипта, в котором добавляю 2 переменных с датой начала и окончания выборки @ds и @de:

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.#otchet') IS NOT NULL
        DROP TABLE #otchet
       GO
       CREATE TABLE #otchet
        (
       	BASE_NAME VARCHAR(60),
       	Raspisanie INT
       )
        
DECLARE @DBName VARCHAR(60),
        @script VARCHAR(MAX),
        @ds DATETIME,
        @de DATETIME
SET @ds = '2014.07.01 0:00:00'
SET @de = '2014.07.31 23:59:59'
DECLARE dbs CURSOR LOCAL FORWARD_ONLY FOR
SELECT
  name
FROM sys.databases
WHERE name LIKE 'EMISZ\_KO\_%' ESCAPE '\'
OPEN dbs
FETCH NEXT FROM dbs INTO @DBName
WHILE @@fetch_status = 0
BEGIN
  PRINT '----------Выбрал базу' + CAST(@DBName AS VARCHAR(60))
  SET @script = 'Use [' + @DBName + '] Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + '.dbo.PLANNING WHERE ' + @DBName + '.dbo.PLANNING.DATE_CONS >= ''' + @ds + ''' AND ' + @DBName + '.dbo.PLANNING.DATE_CONS <= ''' + @de + ''')
  INSERT INTO #otchet (BASE_NAME, Raspisanie) VALUES (''' + @DBName + ''', @RASPISANIE)'
  EXEC (@script)
 
  FETCH NEXT FROM dbs INTO @DBName
END
CLOSE dbs
DEALLOCATE dbs
SELECT
  *
FROM #otchet


однако, получаю ошибку:
------ Выполнение начато: отчет.sql ------
Контекст базы данных изменен на "tempdb".
Выполнение завершено успешно [0,001c]
Выполнение завершено успешно [0,001c]
Ошибка: (18,1): Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.
----------Выбрал базуEMISZ_KO_KGKP
Ошибка: (29,3): Ошибка преобразования даты или времени из символьной строки.
Выполнение закончилось неудачей [0,540c].

Где опять ошибка?
6 ноя 14, 20:38    [16808363]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
romich7
Member

Откуда:
Сообщений: 6
Пробовал вот так:
        @ds VARCHAR(MAX),
        @de VARCHAR(MAX)
SET @ds = '01-07-2014'
SET @de = '31-07-2014'


Ошибка: (7,1): Ошибка преобразования даты или времени из символьной строки.
6 ноя 14, 20:45    [16808390]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
romich7
Member

Откуда:
Сообщений: 6
всё, погуглил, нашел решение:
        @ds VARCHAR(MAX),
        @de VARCHAR(MAX)
SET @ds = '140701'
SET @de = '140731'


Если интересно, то финальный код выглядит так:
+
USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.#otchet') IS NOT NULL
        DROP TABLE #otchet
       GO
       CREATE TABLE #otchet
        (
       	BASE_NAME VARCHAR(60),
       	Raspisanie INT
       )
        
DECLARE @DBName VARCHAR(60),
        @script VARCHAR(MAX),
        @ds VARCHAR(MAX),
        @de VARCHAR(MAX)
SET @ds = '141001'
SET @de = '141031'
DECLARE dbs CURSOR LOCAL FORWARD_ONLY FOR
SELECT
  name
FROM sys.databases
WHERE name LIKE 'EMISZ\_KO\_%' ESCAPE '\'
OPEN dbs
FETCH NEXT FROM dbs INTO @DBName
WHILE @@fetch_status = 0
BEGIN
  PRINT '----------Выбрал базу' + CAST(@DBName AS VARCHAR(60))
  SET @script = 'Use [' + @DBName + '] Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + '.dbo.PLANNING WHERE ' + @DBName + '.dbo.PLANNING.DATE_CONS >= ''' + @ds + ''' AND ' + @DBName + '.dbo.PLANNING.DATE_CONS <= ''' + @de + ''')
  INSERT INTO #otchet (BASE_NAME, Raspisanie) VALUES (''' + @DBName + ''', @RASPISANIE)'
  EXEC (@script)
 
  FETCH NEXT FROM dbs INTO @DBName
END
CLOSE dbs
DEALLOCATE dbs
SELECT
  *
FROM #otchet
6 ноя 14, 21:46    [16808734]     Ответить | Цитировать Сообщить модератору
 Re: Вставка значений выборки курсора во временную таблицу (через переменные). Перебор ДБ.  [new]
romich7
Member

Откуда:
Сообщений: 6
Ну и, собственно, то, к чему стремился - три выборки с переменными в exec внутри курсора и передача данных во временную таблицу:

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.#otchet') IS NOT NULL
        DROP TABLE #otchet
       GO
       CREATE TABLE #otchet
        (
       	BASE_NAME VARCHAR(60),
       	Raspisanie INT,
        Talon INT,
        EMC INT
       )
        
DECLARE @DBName VARCHAR(60),
        @script VARCHAR(MAX),
        @ds VARCHAR(MAX),
        @de VARCHAR(MAX)
SET @ds = '141001'
SET @de = '141031'
DECLARE dbs CURSOR LOCAL FORWARD_ONLY FOR
SELECT
  name
FROM sys.databases
WHERE name LIKE 'EMISZ\_KO\_%' ESCAPE '\'
OPEN dbs
FETCH NEXT FROM dbs INTO @DBName
WHILE @@fetch_status = 0
BEGIN
  PRINT '----------Выбрал базу' + CAST(@DBName AS VARCHAR(60))
  SET @script = 'Use [' + @DBName + '] 
  Declare @RASPISANIE INT SET @RASPISANIE = (SELECT COUNT (*) FROM ' + @DBName + '.dbo.PLANNING WHERE ' + @DBName + '.dbo.PLANNING.DATE_CONS >= ''' + @ds + ''' AND ' + @DBName + '.dbo.PLANNING.DATE_CONS <= ''' + @de + ''')
  Declare @TALON INT SET @TALON = (SELECT COUNT (*) FROM ' + @DBName + '.dbo.FM_BILL WHERE ' + @DBName + '.dbo.FM_BILL.DATE_CREATE >= ''' + @ds + ''' AND ' + @DBName + '.dbo.FM_BILL.DATE_CREATE <= ''' + @de + ''')
  Declare @EMC INT SET @EMC = (SELECT COUNT (*) MOTCONSU FROM ' + @DBName + '.dbo.MOTCONSU WHERE ' + @DBName + '.dbo.MOTCONSU.DATE_CONSULTATION >= ''' + @ds + ''' AND ' + @DBName + '.dbo.MOTCONSU.DATE_CONSULTATION <= ''' + @de + ''')
  INSERT INTO #otchet (BASE_NAME, Raspisanie, Talon, EMC) VALUES (''' + @DBName + ''', @RASPISANIE, @TALON, @EMC)'
  EXEC (@script)
 
  FETCH NEXT FROM dbs INTO @DBName
END
CLOSE dbs
DEALLOCATE dbs
SELECT
  *
FROM #otchet o
DROP TABLE #otchet


Спасибо, Mind!
6 ноя 14, 22:03    [16808848]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить