0

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?

enter image description here

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.


Update

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

--etc
1
  • You must insert new record into Model table with new (altered) values, then obtain ID of this new record and use its value when inserting records in another tables referenced to Model table.
    – Akina
    Dec 10, 2018 at 11:35

1 Answer 1

0

Modelling algorythm.

Assume we have Main table with the fields ID and Name. Assume we have table Details referenced to Main table via ID_Main field (FK) and having Detail field.

Assume we selected the record with some ID from table Main with some name. We altered this name, and now we want to create new records pack - with new Name and with the copy of Detail value from source record.

First, we must ensure that new name value is not present in Main table (we do not like full duplicates, is it?). So we query

SELECT COUNT(*) FROM Main WHERE Name = 'New Name Value'

and check that its result is zero ('0'). If not, this value is already present, and we tell it to operator.

If the value returned is zero, we can store data. We insert it with the query

INSERT INTO Main (Name) VALUES ('New Name Value')

The record is inserted, and some ID is assumed to it. We may:

1) obtain it by simple query

SELECT id FROM Main WHERE Name = 'New Name Value'

and then use this value in the query which adds data to the table Details referenced to this record

INSERT INTO Details (Main_ID, Detail) VALUES ('obtained ID value', 'New Detail value')

2) do not obtain ID separately, doing this in a INSERT query

INSERT INTO Details (Main_ID, Detail) 
SELECT id, 'New Detail value' FROM Main WHERE Name = 'New Name Value'

PS. For SQL Server you can obtain ID of last record using select scope_identity(), select @@identity or select ident_curent(‘tablename’) dependent by what you need.

3
  • Thanks for taking the time to provide an answer, however this isn't exactly what i had inteded to ask in my question. The idea is to copy all rows from multiple tables that have the same model_ID and then from there replace the model_ID to match the new model. This way the user is interacting with an enitrely different model whilst having the default values being that of the originally selected model.
    – J. Spence
    Dec 10, 2018 at 12:04
  • @J.Spence The idea is to copy all rows from multiple tables that have the same model_ID and then from there replace the model_ID to match the new model. Look at the last variant (2) - it performs directly the same. It copies the dependent record(s) and replaces reference ID value with new one.
    – Akina
    Dec 10, 2018 at 12:44
  • Apologies, My mistake. Ideally i was hoping to not have to replicate code for the task but if that is the solution thank you!
    – J. Spence
    Dec 10, 2018 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.