I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be used as a default/baseline for a new Model when editing.
My question would be, how does one copy every single row from multiple tables where the foreign key matches the Model_ID of the selected Model to then be used in a new Model?
Would this require selecting the rows from the tables into a tempdb and then re-inserting into the originaldb, this seems quite resource heavy for such a task. Is there a better solution?
I've attached an img of the database design I have created just in case it is of use. help and advice would be greatly appreciated, Thank You.
After taking the suggestion of Akina, this is my plan on how the rows will be duplicated for user usage (Pseudocode). If there are any glaring errors feel free to correct.
@X int -- the selected existing Model taken as a paremeter
@new_model_Name -- fields to be entered into the model table
@new_model_completionUser -- fields to be entered into the model table
@new_model_dateCreated -- fields to be entered into the model table
insert into model values (new_model_Name, new_model_completionUser, new_model_dateCreated)
insert into table1 (old_data1, old_data2,..., New_Model_ID)
select * from table1 where Model_ID = X
insert into table2 (old_data1, old_data2,..., New_Model_ID)
select * from table2 where Model_ID = X