Sql-server – Create Copy of all rows where foreign key matches specified and then replace foreign key

database-designreplicationsql server

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

Best Answer

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.

Related Question