Best answer by Kevin Horst
Hi Hauke,
In the current upcycle script there is no functionality which imports the definition of the views.
There are two reasons for that:
Luckily we do have the functionality available to import the view definitions into your model:
View original
In the current upcycle script there is no functionality which imports the definition of the views.
There are two reasons for that:
- We need to generate the definition of the model first
- Every database saves the 'Query' of the view a little bit differently, so most of the time you need to ever slightly change the import script.
Luckily we do have the functionality available to import the view definitions into your model:
code:
--How to:
--Step 0 First do the main upcyle script and generate the definitions
--Step 1 Fill in the parameters below
--Step 2 Replace DEMO_SF -> SF database
--Step 3 Replace [demo] -> Upcycle databasedemo
Declare @project_id varchar(150) = 'DEMO'
,@project_vrs_id varchar(150) = '1.00'
--Step 4 Execute (F5)
----------First the control procedure for all the views
insert into DEMO_sf..[control_proc]
(project_id
,project_vrs_id
,control_proc_id
,control_proc_desc
,control_proc_type
,assign_type
,code_grp_id
,gen_order_no
,development_status
,priority
,reviewed
,tested
,generated)
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,concat('Upcycled ', v.name, ' view') as control_proc_desc
,1 as control_proc_type
,0 as assign_type --Static
,'VIEWS' as code_grp_id
,100 as gen_order_no
,0 as development_status
,7 as priority
,0 as reviewed
,0 as tested
,0 as generated
FROM [demo].sys.views v
----the create the templates with the default definition
insert into DEMO_sf..[control_proc_template]
(project_id
,project_vrs_id
,control_proc_id
,template_id
,template_desc
,order_no
,no_of_object_item
,template_code
,code_generated)
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
,concat(
'Upcycled definition of the '
, v.name
, ' view'
) as template_desc
,100 as order_no
,1 as no_of_object_item
,right(m.definition
,len(m.definition)
- CHARINDEX('SELECT',m.definition) + 1
) as template_code
,0 as code_generated
FROM [demo].sys.views v
join [demo].sys.schemas s
on s.schema_id = v.schema_id
INNER JOIN [demo].sys.sql_modules m ON m.object_id = v.object_id
--Now allocate the templates with the vies
----First generate the definition!
insert into DEMO_sf..prog_object_item
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('view_',v.name) as prog_object_id
,v.name as prog_object_item_id
,100 as order_no
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
FROM [demo].sys.views v
