Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
Не по теме. Прямо так и пишете (без указания длины)? В MSSQL это эквивалентно convert(varchar(30),field). |
||
29 май 13, 13:36 [14364464] Ответить | Цитировать Сообщить модератору |
AlexGru Member Откуда: Пермь рядом Сообщений: 1600 |
Ага, действительно, все до 30 урезается, но у меня и данные такие пока. Пока вроде не потерял ничего. Можно так convert(varchar(4000),filed) as field ?? |
29 май 13, 13:43 [14364530] Ответить | Цитировать Сообщить модератору |
AlexGru Member Откуда: Пермь рядом Сообщений: 1600 |
А как все таки автоматизировать |
29 май 13, 13:55 [14364611] Ответить | Цитировать Сообщить модератору |
Гость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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
AlexGru, А, ну ясно, тоже через запрос к метаданным. Я уж подумал, что есть какая-то магическая функция в каком-нибудь DBMS_SQL ![]() |
29 май 13, 15:37 [14365298] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
А если убрать "ORDER BY c.ORDINAL_POSITION"? |
||
29 май 13, 15:39 [14365309] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47050 |
AlexGru, Это недокументированная фича. Вам не зря про ORDER BY сказали. Лучше строки конкатенировать конструкцией FOR XML PATH(''). И имена объектов надо бы сделать юникодными (для порядка). Есть же специальный тип sysname |
29 май 13, 15:56 [14365437] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
А вы видели, что вы получаете в своей переменной @s ? |
||
29 май 13, 15:59 [14365465] Ответить | Цитировать Сообщить модератору |
AlexGru Member Откуда: Пермь рядом Сообщений: 1600 |
Glory, нет строку не вижу, как-будто пустоту выводит и всё. |
29 май 13, 16:12 [14365554] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Потому что print-ы смотрят в закладке Messages |
||
29 май 13, 16:13 [14365565] Ответить | Цитировать Сообщить модератору |
AlexGru Member Откуда: Пермь рядом Сообщений: 1600 |
Я там и смотрю |
29 май 13, 16:17 [14365588] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Значит ваша переменная пуста А пуста она потому, что вы не присвоили ей начальное значение А в MSSQL NULL+что-то всегда равняется NULL |
||
29 май 13, 16:20 [14365614] Ответить | Цитировать Сообщить модератору |
AlexGru Member Откуда: Пермь рядом Сообщений: 1600 |
Прекрасно. Спасибо большое. Дальше буду копать |
29 май 13, 16:21 [14365622] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
А всякие курсоры, туча переменных, WHILE и другую императивную шелуху фтопку. |
||
29 май 13, 16:23 [14365635] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
Вы зря убрали условие на TABLE_SCHEMA. Одно имя таблицы не идентифицирует однозначно объект. |
||
29 май 13, 16:32 [14365694] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
https://www.sql.ru/faq/faq_topic.aspx?fid=130 |
||
29 май 13, 16:41 [14365763] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |