Update Id on INSERT INTO SELECT FROM statement

Question

i want to copy specific columns from Table [From] to Table [To] but also want to insert the foreign key from [To] in [From]

Table definitions:

[From]
Id    (int)
pic   (varbinary(MAX))
picId (int)

[To]
Id (int)
pic (varbinary(MAX))

My copy query works perfectly but i dont know how to update the "picId" column inside of the Table [From]

INSERT INTO dbo.[To] (Id,pic)
SELECT 
    isnull(T.m, 0) + row_number() over (order by F.pic),
    F.pic
FROM dbo.[From] AS F
    outer apply (select max(pic) as m from dbo.[To]) as T;

Now i want to copy the inserted [To].Id to [From].picId.

Can anyone help me please?

are you sure the query is correct ? Is it supposed to be `max(pic)` or `max(id)` ? — Squirrel, Dec 19 '22 at 10:27
@Squirrel you are right. But i need this query only on a emtpy table, so i think this is not neccessary — zTim, Dec 19 '22 at 10:36
See how to use [output](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) — Stu, Dec 19 '22 at 10:40
as you have a 1:1 relqatioship, add from_id to the table To, so you don't eeed to do much — nbk, Dec 19 '22 at 10:48
@Squirrel but how can i implement this in my query? I dont get it because of the multiple rows to output — zTim, Dec 19 '22 at 11:15
Could you allow a columns FromID in your table dbo.[to]? If so, you could populate that with your FromID and then cross reference back to update dbo.[From].ToID. I can write this up as a full solution if you can allow the extra column and want it written out, just let me know — Robert Sheahan, Dec 19 '22 at 16:32

score 1 · Accepted Answer · answered Dec 19 '22 at 18:52

2 changes would make your solution much easier, but assuming you can't control anything about the dbo.[TO] table, here is a solution that will work for you.

The first improvement would be making dbo.[to].ID an identity column. Then you could drop your whole "row_number() over" line and let SQL manage the ID. What you're doing works, but it's like cutting wood with a chain saw by dragging the (not running) saw back and forth over the wood. You can do it, but it's a lot easier if you start the engine and let it do the work.

The second change is adding a column dbo.[to].FromID and populating it when you insert the row. The OUTPUT statement can only reference fields in the row being inserted (or deleted, but that's not relevant here) so you can't get the ID of the row in dbo.[from] that you want to update unless you have it in the row in dbo.[to] you just inserted. If you do this, you can use a plain old INSERT with an OUTPUT clause. The trick here is using a MERGE statement, and you can see a full explanation here: Is it possible to for SQL Output clause to return a column not being inserted? I strongly urge you to upvote that answer if you find this useful. I could not have provided you with this without that answer!

Anyway, here is the solution:

--Create some fake data, you'll already have dbo.[From]
CREATE TABLE #TestFrom (FromID INT, Pic nvarchar(1000), ToID INT NULL)
CREATE TABLE #TestTo (ToID INT, Pic nvarchar(1000))

--It would be much easier if your TO used an IDENTITY column instead of managing the ID manually
CREATE TABLE #TestToIdent (ToID INT IDENTITY(1,1), Pic nvarchar(1000))

INSERT INTO #TestFrom 
VALUES (1, 'Test 1', NULL)
        , (3, 'Test 3', NULL)
        , (7, 'Test 7', NULL)
        , (13, 'Test 13', NULL)

--Define a table variable to hold your OUTPUT, you'll need this
DECLARE @Mapping as table (FromID INT, ToID INT);

with cteIns as (
    SELECT 
        isnull(T.m, 0) + row_number() over (order by F.pic) as ToID
        , F.pic, F.FromID 
    FROM #TestFrom  AS F
        outer apply (select max(ToID) as m from #TestTo ) as T
) --From https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted 
MERGE INTO #TestTo as T --Put results here
    USING cteIns as F --Source is here
        on 0=1 --But this is never true so never merge, just INSERT
WHEN NOT MATCHED THEN --ALWAYS because 0 never = 1
    INSERT (ToID, pic)
    VALUES (F.ToID, F.pic)
    OUTPUT F.FromID, inserted.ToID
    INTO @Mapping (FromID, ToID );
    
--SELECT * FROM @Mapping --Test the mapping if you're curious
--SELECT * FROM #TestTo --Test the insedert if you're curious

--Update the dbo.[FROM] with the ID of the [TO] that got inserted
UPDATE #TestFrom SET ToID = M.ToID 
FROM #TestFrom as F 
    INNER JOIN @Mapping as M 
        ON M.FromID = F.FromID 

--View the results
SELECT * FROM #TestFrom

DROP TABLE #TestFrom
DROP TABLE #TestTo 
DROP TABLE #TestToIdent

Update Id on INSERT INTO SELECT FROM statement

1 Answers1