Insert on a child table and update FK on parent

Question

I have a parent table with the following structure and data:

---------------------------------------------
|  Id | TranslationId |                Name |
---------------------------------------------
|   1 |          NULL |          Image1.jpg |
|   2 |          NULL |          Image7.jpg |
|   3 |          NULL |    Picture_Test.png |
---------------------------------------------

And the empty child table which holds the translated images:

-------------------------------------------------------------------------
|  Id |                  De |                  Fr |                  En |
-------------------------------------------------------------------------
|     |                     |                     |                     |
-------------------------------------------------------------------------

Now I'm looking for a single query statement or at least few queries which I can run without any further programming. Doing this job with scripting or programming would be easy but I have often situations where I need this kind of insert / update. And developing each time a small console app is not feasible.

At the end the two tables should look like this:

---------------------------------------------
|  Id | TranslationId |                Name |
---------------------------------------------
|   1 |            28 |                NULL |
|   2 |            29 |                NULL |
|   3 |            30 |                NULL |
---------------------------------------------

-------------------------------------------------------------------------
|  Id |                  De |                  Fr |                  En |
-------------------------------------------------------------------------
|  28 |          Image1.jpg |                NULL |               NULL  |
|  29 |          Image7.jpg |                NULL |               NULL  |
|  30 |    Picture_Test.png |                NULL |               NULL  |
-------------------------------------------------------------------------

Thank you for any advice.

How to get TranslationID when there are NULLs in your parent table? — M. Kanarkowski, May 30 '19 at 12:56
`Id` is an identity column. 1. Insert on child table with De = Name of parent 2. Update parent's TranslationId = Child's (new) id — dannyyy, May 30 '19 at 13:00
use SCOPE_IDENTITY() while inserting in the child table and get the Id value of child table. Later, update the Parent table to fill TranslationId. If the child table has to refer to the parent table TranslationId, use a Sequence object instead of Identity and get the unique value to insert into both the tables. — Dheerendra, May 30 '19 at 13:04
Use OUTPUT clause https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017 to gather inserted ids to update original table. — Serg, May 30 '19 at 13:04
Serg and Sheerandra you are all right. I'm aware of this keywords. But in a regular application I would do a loop (foreach, for, ...). I'm wondering how to achieve this in SQL? CTE, Cursor, ... ? — dannyyy, May 30 '19 at 13:06
Output to `@temp` table, update the original table from it. Two DML statements, no loops. — Serg, May 30 '19 at 13:08
If you allowed to change the table structure, I would recommend you to remove identity and use SEQUENCE. With sequence you can update the parent table with the child key, after that you can use insert with no restriction. — Zeki Gumus, May 30 '19 at 13:26
It's not feasible to change anything on the scheme. We have legacy systems which write the data in an old way and we are not able to change the inserting system due to licensing issues. I need this kind of SQL for a migration tasks I regulary have to perform. But it seem's like there is no anwer for this question. Then I will do it with good old CURSOR. — dannyyy, May 30 '19 at 13:29

Dheerendra · Answer 1 · 2019-05-30T13:39:03.813

1

You could do it something like the below :

INSERT INTO Child
(
   Id
   ,De
   ,Fr
   ,En
)
OUTPUT Inserted.Id INTO #Temp
SELECT Id
       ,De
       ,Fr
       ,En
FROM @Values --If you are using a table type to insert into the Child table as a set based approach

;WITH CTE
AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY Id) AS Rnk
  ,Id
  FROM #Temp

)
,CTE1 AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY Id) AS Rnk
  ,*
  FROM Parent
)

UPDATE cte1
SET TranslationId = cte.Id
FROM CTE1 cte1
JOIN CTE cte ON cte.Rnk = cte1.Rnk

edited May 30 '19 at 13:39

answered May 30 '19 at 13:16

Dheerendra

284
1
7

I tried your approach, but I guess it's not working with identity columns (on parent and child). I tried it in a slightly different way and the data were messed up. – dannyyy May 30 '19 at 13:33
I have edited the code to apply the sequence on parent table as well and joining based on the sequence. Try the code and let me know. – Dheerendra May 30 '19 at 13:39

score 1 · Answer 2 · answered May 30 '19 at 13:49

Demo, assuming Name is unique in the first table

create table tab1 (
  id int identity
 ,TranslationId int null
 ,Name nvarchar(max) null
 );
 insert tab1 (Name)
     values 
     ('Image1.jpg')
    ,('Image7.jpg')
    ,('Picture_Test.png')
    ,(null)

create table tab2 (
  id int identity (100,1)
 ,De nvarchar(max) null
 ,Fr nvarchar(max) null
 ,En nvarchar(max) null
 );

-- Update them 

declare @map table(
  name nvarchar(max) 
 ,ref int
  );

insert tab2 (de)
output inserted.De, inserted.id
into @map(Name, ref)
select Name
from tab1 src
where Name is not null and not exists (select 1 from tab2 t2 where t2.De = src.Name);

update t1 set TranslationId = ref, Name = null
from tab1 t1
join @map m on t1.Name = m.Name;

select * from tab1;
select * from tab2;

score 0 · Accepted Answer · answered May 30 '19 at 14:03

I figured out in the meantime how to do it. Applied on the database, the query looks like this:

DECLARE @Temp TABLE (ImageId INT, Id INT)

MERGE INTO Translation USING 
  (
  SELECT Image.Name AS Name, Image.Id AS ImageId
  FROM Candidate
  INNER JOIN Candidacy ON Candidate.Id = Candidacy.CandidateId
  INNER JOIN Election ON Candidacy.ElectionId = Election.Id
  INNER JOIN SmartVoteCandidate ON Candidate.Id = SmartVoteCandidate.CandidateId
  INNER JOIN Image ON SmartVoteCandidate.SpiderImageId = Image.Id
  WHERE Election.Id = 1575) AS temp ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (De)
  VALUES (temp.Name)
  OUTPUT temp.ImageId, INSERTED.Id
  INTO @Temp (ImageId, Id);

UPDATE Image
SET Image.TranslationId = t.Id, Name = NULL
FROM @Temp t
WHERE Image.Id = t.ImageId

The solution is heavily inspired by

Insert on a child table and update FK on parent

3 Answers3