Hi Tejo,

You can create a SQL-typed control procedure in which you dynamically assign templates and replace parameters of your control procedure template based on data within your model.

Your template could be something like this:

insert into [tab]
(
    [col][comma]
)
select 
    [SELECT_COL][SELECT_COMMA]
from [tab]
where 1=1 -- Makes it easier ;)
  and [WHERE_CLAUSE]    

Inside the Control procedure SQL code (Staged strategy), you can add records in #prog_object_item for each time you want to weave a template. Add records into #prog_object_item_parmtr  for each parameter you want to replace.

Most of the generated control procedures we distribute use this method. You can disable the Hide generated prefilter to see these control procedures.

Hope this gives some inspiration 

ha, that’s inspiration allright  I’m gonna spell it out word by word to understand and get back to you, thanks!

Maybe this will give you a head start  

insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select  
    'task_copy_data',
    'task_copy_data_' + t.tab_id,    
    10,
    'template_name'
from tab t
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'task_copy_data',
    'task_copy_data_' + t.tab_id,  
    'COL',
    c.col_id,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = t.model_id 
 and c.branch_id = t.branch_id
 and c.tab_id = t.tab_id
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  
  and c.calulated_field_type = 0 -- Only non-calculated fields

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'task_copy_data',
    'task_copy_data_' + t.tab_id,  
    'COMMA',
    case max(c.abs_order_no) over (partition by c.tab_id)
       when c.abs_order_no then ' '
       else ', '
    end,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = t.model_id 
 and c.branch_id = t.branch_id
 and c.tab_id = t.tab_id
where t.model_id = @model_id 
  and t.branch_id = @branch_id
  and t.type_of_table = 0 -- Only tables  
  and c.calulated_field_type = 0 -- Only non-calculated fields

(This can indeed be quite complex, but this will make is dynamic. So if any table names or column names change, the procedure will be adjusted accordingly)

Note that you may need to look into the ref table to find the references from the object you want to start copying.

thanks, I see now how the many task_copy procedures are created in SF! Some homework to do here.

It's indeed crazy 

Just the SQL code of the Control procedure by itself that weaves in the template code for all the copy tasks is total 2067 lines of code (including comments and break lines). Using 12 different templates, and some other trickery for special situations, it's one of the more complex piece of code in the Software Factory. 

One last thing, you can utilize the table-valued function dbo.get_children in your code to determine all children of a particular table. You can use it like this:

select *
from dbo.get_children(@model_id, @branch_id, 'Table', 1 -- Pure references

It will return the references, tab_id and the depth of the references.

ok, getting the vibe here! I added the c.include_in_copy=1 when getting the columns.

1 final question, how do you get the initial copy_to_id? in our case these are identity seeds, so when inserting the details, I have to use the main table’s id. Is there a Thinkwise ‘trick’ to get this one? Or just use the @@identity?

We typically use scope_identity()

declare @new_id bigint = scope_identity()

Hi @Mark Jongeling, any thoughts on the message “The MERGE statement conflicted with the FOREIGN KEY constraint "ref_prog_object_prog_object_item". The conflict occurred in database "SF", table "dbo.prog_object".” when generating definition?

Even with the default code when creating a new procedure.

I’ve added project_id and project_vrs_id (seen in examples with Delete strategy) but this doesn’t help

added task control procedure:

changed default code

using 2022.2 up to hotfix 20230313

Hi Tejo,

It looks like the Prog object is not created yet. If you generate the code group "Tasks" that should fix it. If it doesn't, you may not have a task called test (object name task_test). Create that task, generate code group and the code should run successfully.

jep, found it, typo in task name. Grazi!