Copy records-data within same Child table but with new Parent ID from Parent table?

Question

Hello, my question here in reference to SQL 2016...

Here's my example to simplify.

I have two tables within same DB as follows.

Parent table:

CREATE TABLE [dbo].Machine ON [PRIMARY]
GO

Child table:

CREATE TABLE [dbo].MachineContacts ON [PRIMARY]
GO

ALTER TABLE [dbo].[MachineContacts] WITH CHECK ADD CONSTRAINT [FK_MachineID] FOREIGN KEY([MachineID])
REFERENCES [dbo].[Machine] ([MachineID])
GO

ALTER TABLE [dbo].[MachineContacts] CHECK CONSTRAINT [FK_MachineID]
GO

Here are all of the records in Parent table:

select * from [dbo].[Machine]

MachineId MachineName MachineStatus
1 Server1 Active
2 Server1 Inactive

Here are all of the records in it's Child table (just one for now):

select * from [dbo].[MachineContacts]

MachineContactsID ContactName ContactEmail MachineID
1 Peter Peter@ssss .com 1

What is/are the SQL statement(s) required to make a duplicate copy of existing one record within Child table but with the latest MachineID?

The goal is for Child table to have two records as follows:

MachineContactsID ContactName ContactEmail MachineID
1 Peter Peter@ssss .com 1
2 Peter Peter@ssss .com 2

Both tables actually have many more records but trying to simplify examples above and trying to figure out the logic of the SQL statement(s) needed.

Looks like Join(s) will be needed perhaps. If necessary to run multiple SQL Inserts/Updates statements instead of just one am open ears.

Thanks in advance.

Answer 1

Hi @techresearch7777777
Please check this:

;WITH CTE AS  
(  
 SELECT TOP(1) ContactName,ContactEmail,(SELECT MAX(MachineId) FROM Machine)AS MachineID  
 FROM MachineContacts  
 ORDER BY MachineContactsID DESC  
)  
INSERT INTO MachineContacts   
SELECT * FROM CTE  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Thanks Erland for your quick reply.

Am wondering how did the new record within MachineContacts get the correct MachineID value of 2 without [Machine] table being referred within your SQL statement?

When adding new records in Parent table [Machine] like:

select * from [dbo].[Machine]

MachineId MachineName MachineStatus
1 Server1 Active
2 Server1 Inactive
3 Server2 Active
4 Server2 Inactive

And after running your query again Child table [MachineContacts].[MachineID] value '4' doesn't haven't a new corresponding entry record.

Regards.

Answer 3

Sorry I forgot to also say that a new record within Child table as:

MachineContactsID ContactName ContactEmail MachineID
1 Peter Peter@ssss .com 1
2 Peter Peter@ssss .com 2
3 Tom Tom@ssss .com 3

I would then like run a query that references Parent table [Machine] using it's [MachineID] value of '4' and copy new entry of Tom's data as:

MachineContactsID ContactName ContactEmail MachineID
1 Peter Peter@ssss .com 1
2 Peter Peter@ssss .com 2
3 Tom Tom@ssss .com 3
3 Tom Tom@ssss .com 4

Answer 4

Thanks bunch LiHongMSFT-3908 your provided seems to have been the solution.

And yes that last record in my example yellow highlight should be 4 instead of 3.