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

Откуда: Пермь рядом
Сообщений: 1600
День добрый.
Переношу данные из MSSQL в Oracle, через гетерогенный сервис.
Методом CTAS. Всё бы прекрасно, но вот так не видны поля типа nvarchar.
Приходится в MSSQL делать вьюхи и конвертировать эти поля методом
...
convert(varchar,field) as field
...
Тогда в Oracle CTAS эти поля видит как надо.
Сейчас надо скопом перенести порядка 30 таблиц и в каждой по 1,2,3 поля с nvarchar.
Можно ли в MSSQL написать скрипт/запрос которой передаем имя таблицы, а она создает вьюху, с нужной мне конвертацией, вместо того чтобы руками каждый раз явно все поля приписывать.
?

В Oracle это было бы сделать тривиально, но с MSSQL Не знаком вообще.
Спасибо.
29 май 13, 13:32    [14364443]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AlexGru
convert(varchar,field) as field

Не по теме. Прямо так и пишете (без указания длины)? В MSSQL это эквивалентно convert(varchar(30),field).
29 май 13, 13:36    [14364464]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
Ага, действительно, все до 30 урезается, но у меня и данные такие пока.
Пока вроде не потерял ничего.
Можно так
convert(varchar(4000),filed) as field
??
29 май 13, 13:43    [14364530]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
А как все таки автоматизировать
29 май 13, 13:55    [14364611]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Гость333
Member

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

В переменные @schema и @table подставляете свои значения:
USE AdventureWorks;

DECLARE @schema sysname, @table sysname;

SELECT @schema = N'Production', @table = N'Product';

SELECT 1 AS ordr, N'CREATE VIEW ' + QUOTENAME(@schema) + N'.' + QUOTENAME(N'view_' + @table) + N' AS SELECT' AS string
UNION ALL
SELECT c.ORDINAL_POSITION + 1, CASE WHEN c.ORDINAL_POSITION = 1 THEN N'' ELSE N',' END +
       CASE
           WHEN c.DATA_TYPE IN (N'nchar', N'nvarchar')
           THEN N'CONVERT(' + STUFF(c.DATA_TYPE, 1, 1, N'') + N'(' + CONVERT(nvarchar(30), c.CHARACTER_MAXIMUM_LENGTH) + N'), ' + QUOTENAME(c.COLUMN_NAME) + N') AS '
           ELSE N''
       END +
       QUOTENAME(c.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table
UNION ALL
SELECT 100000, N'FROM ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table)
ORDER BY ordr;

В итоге в столбце string получаете такое:
CREATE VIEW [Production].[view_Product] AS SELECT
   [ProductID]
  ,CONVERT(varchar(50), [Name]) AS [Name]
  ,CONVERT(varchar(25), [ProductNumber]) AS [ProductNumber]
  ,[MakeFlag]
  ,[FinishedGoodsFlag]
  ,CONVERT(varchar(15), [Color]) AS [Color]
  ,[SafetyStockLevel]
  ,[ReorderPoint]
  ,[StandardCost]
  ,[ListPrice]
  ,CONVERT(varchar(5), [Size]) AS [Size]
  ,CONVERT(char(3), [SizeUnitMeasureCode]) AS [SizeUnitMeasureCode]
  ,CONVERT(char(3), [WeightUnitMeasureCode]) AS [WeightUnitMeasureCode]
  ,[Weight]
  ,[DaysToManufacture]
  ,CONVERT(char(2), [ProductLine]) AS [ProductLine]
  ,CONVERT(char(2), [Class]) AS [Class]
  ,CONVERT(char(2), [Style]) AS [Style]
  ,[ProductSubcategoryID]
  ,[ProductModelID]
  ,[SellStartDate]
  ,[SellEndDate]
  ,[DiscontinuedDate]
  ,[rowguid]
  ,[ModifiedDate]
FROM [Production].[Product]


А как это делается в Oracle?
29 май 13, 14:18    [14364717]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
Вот пример.

declare
 v_tbl varchar2(32):='EURUSD_TICKS' ;
 v_sql varchar2(2000);
begin
FOR FIELDS IN (select
                     (CASE c.DATA_TYPE WHEN 'NUMBER' THEN ' to_char('||c.COLUMN_NAME||') AS '||c.COLUMN_NAME ELSE c.COLUMN_NAME END) as fld  
                from user_tab_cols c where c.TABLE_NAME='EURUSD_TICKS' 
               order by c.COLUMN_ID) LOOP
 IF v_sql IS NULL THEN
 v_sql:=FIELDS.fld;
 ELSE
 v_sql:=v_sql||' ,'||FIELDS.fld;
 END IF;
END LOOP;
 dbms_output.put_line(v_sql);
 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW V_'||v_tbl||' AS SELECT '||v_sql||' FROM '||v_tbl;
end;
29 май 13, 15:32    [14365259]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
У меня что-то не получается.
Вышло такое

declare 
@s varchar(2000),
@t varchar(1000),
@tableName varchar(1000)
select @tableName=N'T40000533'
SELECT @s=@s+','+CASE WHEN c.ORDINAL_POSITION = 1 THEN N'' ELSE N',' END + CASE
           WHEN c.DATA_TYPE IN ('nchar','nvarchar')
           THEN N'CONVERT(' + STUFF(c.DATA_TYPE, 1, 1, N'') + N'(' + CONVERT(nvarchar(30), c.CHARACTER_MAXIMUM_LENGTH) + N'), ' + QUOTENAME(c.COLUMN_NAME) + N') AS '
           ELSE N''
       END +
       QUOTENAME(c.COLUMN_NAME)
FROM [DWH\DWH].[CDW_DEMO_R26].INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @tableName
ORDER BY c.ORDINAL_POSITION
select @s=substring(@s,2,len(@s)-1)
print(@s)
select @t=N'CREATE VIEW V_'+@tableName+N' as select '+@s+N' from [DWH\DWH].[CDW_DEMO_R26].[dbo].['+@tableName+']'
exec(@t)


При этом вот так представление создается
CREATE VIEW V_T40000533 as select * from [DWH\DWH].[CDW_DEMO_R26].[dbo].[T40000533]
29 май 13, 15:35    [14365281]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Гость333
Member

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

А, ну ясно, тоже через запрос к метаданным. Я уж подумал, что есть какая-то магическая функция в каком-нибудь DBMS_SQL
29 май 13, 15:37    [14365298]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AlexGru
У меня что-то не получается.
Вышло такое

А если убрать "ORDER BY c.ORDINAL_POSITION"?
29 май 13, 15:39    [14365309]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
А что в этом не правильно??
Запарился совсем

declare 
@s varchar(2000),
@t varchar(1000),
@tableName varchar(1000)
select @tableName=N'T40000533'
SELECT @s=@s+','+CASE WHEN c.ORDINAL_POSITION = 1 THEN N'' ELSE N',' END + CASE
           WHEN c.DATA_TYPE IN ('nchar','nvarchar')
           THEN N'CONVERT(' + STUFF(c.DATA_TYPE, 1, 1, N'') + N'(' + CONVERT(nvarchar(30), c.CHARACTER_MAXIMUM_LENGTH) + N'), ' + QUOTENAME(c.COLUMN_NAME) + N') AS '
           ELSE N''
       END +
       QUOTENAME(c.COLUMN_NAME)
FROM [DWH\DWH].[CDW_DEMO_R26].INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @tableName
ORDER BY c.ORDINAL_POSITION
select @s=substring(@s,2,len(@s)-1)
print(@s)
select @t=N'CREATE VIEW V_'+@tableName+N' as select '+@s+N' from [DWH\DWH].[CDW_DEMO_R26].[dbo].['+@tableName+']'
exec(@t)
29 май 13, 15:50    [14365391]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
iap
Member

Откуда: Москва
Сообщений: 47050
AlexGru,

Это недокументированная фича.
Вам не зря про ORDER BY сказали.
Лучше строки конкатенировать конструкцией FOR XML PATH('').

И имена объектов надо бы сделать юникодными (для порядка).
Есть же специальный тип sysname
29 май 13, 15:56    [14365437]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Glory
Member

Откуда:
Сообщений: 104760
AlexGru
А что в этом не правильно??

А вы видели, что вы получаете в своей переменной @s ?
29 май 13, 15:59    [14365465]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
Glory,

нет строку не вижу, как-будто пустоту выводит и всё.
29 май 13, 16:12    [14365554]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Glory
Member

Откуда:
Сообщений: 104760
AlexGru
нет строку не вижу, как-будто пустоту выводит и всё.

Потому что print-ы смотрят в закладке Messages
29 май 13, 16:13    [14365565]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
Я там и смотрю
29 май 13, 16:17    [14365588]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
AlexGru,

declare
@s varchar(200)
SELECT @s = @s + ','+ c.COLUMN_NAME FROM [DWH\DWH].[CDW_DEMO_R26].INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'T40000533'
print ('-----------------------')
print (@s)
print ('-----------------------')

SELECT c.COLUMN_NAME FROM [DWH\DWH].[CDW_DEMO_R26].INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'T40000533'


даже вот такое.
выводит
-----------------------

-----------------------

Хотя отдельно запрос возвращает столбец с названиями полей
29 май 13, 16:18    [14365603]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Glory
Member

Откуда:
Сообщений: 104760
AlexGru
Я там и смотрю

Значит ваша переменная пуста
А пуста она потому, что вы не присвоили ей начальное значение
А в MSSQL NULL+что-то всегда равняется NULL
29 май 13, 16:20    [14365614]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
AlexGru
Member

Откуда: Пермь рядом
Сообщений: 1600
Прекрасно. Спасибо большое. Дальше буду копать
29 май 13, 16:21    [14365622]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iap
FOR XML PATH('').
+100500
А всякие курсоры, туча переменных, WHILE и другую императивную шелуху фтопку.
29 май 13, 16:23    [14365635]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AlexGru
А что в этом не правильно??
Запарился совсем

select @tableName=N'T40000533'

SELECT ...
FROM [DWH\DWH].[CDW_DEMO_R26].INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @tableName

Вы зря убрали условие на TABLE_SCHEMA. Одно имя таблицы не идентифицирует однозначно объект.
29 май 13, 16:32    [14365694]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт создания вьюх с применением convert  [new]
Glory
Member

Откуда:
Сообщений: 104760
AlexGru
Прекрасно. Спасибо большое. Дальше буду копать

https://www.sql.ru/faq/faq_topic.aspx?fid=130
29 май 13, 16:41    [14365763]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить