Update a table with inserted value of another table

Question

Post reply

Update a table with inserted value of another table

sqlislife

Ten Centuries

Points: 1251

August 11, 2021 at 5:03 pm

#3918872

A good day to all the pros!

I am trying to create a sproc to update the value of a table with the inserted values of another. Here is the DDL for what I need.

--DROP TABLE #FirstTable
Create Table #FirstTable (Id INT IDENTITY(1,1), SName VARCHAR(20))
INSERT INTO #FirstTable (SName)
Select 'SName1' UNION
Select 'SName1.' UNION
Select 'SName1..' UNION
Select 'SName1...'


--DROP TABLE #SecondTable
Create Table #SecondTable (Id INT IDENTITY (1,1), FirstId INT, SecondId INT, ThirdId INT, FourthId INT)
INSERT INTO #SecondTable (FirstId, SecondId, ThirdId, FourthId)
Select 1, 2, 3, 4


Select * from #FirstTable
Select * from #SecondTable

I am trying to create a stored procedure that will update the #SecondTable based on the Id inserts from the first table. For example, if I insert these new 4 rows into the first table, it will create a set of 4 new Ids as seen below;

INSERT INTO #FirstTable (SName)
Select 'SName2' UNION
Select 'SName2.' UNION
Select 'SName2..' UNION
Select 'SName2...'

Select * from #FirstTable
Select * from #SecondTable

Now I want to update the #SecondTable to use the newly inserted values from the #FirstTable

Now I want to update the #SecondTable to use the newly inserted values from the #FirstTable

Here is what my expected result is supposed to look like

Update #SecondTable
SET FirstId = 5, SecondId = 6, ThirdId = 7, FourthId = 8
Where Id = 1

Select * from #SecondTable

How can I go about this? I appreciate any help I can get as this is not intuitive to me at the moment. Thank you all.

This topic was modified 3 years, 3 months ago by sqlislife.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

I have no idea why you want to do this but you could try the pivoting the results of the OUTPUT clause. eg:

DECLARE @ForSecond TABLE
(  
    rn int IDENTITY NOT NULL
PRIMARY KEY
    ,id int NOT NULL
);
INSERT INTO #FirstTable(SName)
OUTPUT inserted.Id
INTO @ForSecond(id)
VALUES ('SName5'),('SName6'),('SName7'),('SName8');
WITH NewValues
AS
(
SELECT [1], [2], [3], [4]  
FROM  
(
  SELECT rn, id 
  FROM @ForSecond
) AS S
PIVOT  
(  
  MAX(id)
  FOR rn IN ([1], [2], [3], [4])  
) AS P
)
UPDATE S
SET FirstId = N.[1]
,SecondId = N.[2]
,ThirdId = N.[3]
,FourthId = N.[4]
FROM #SecondTable S
CROSS JOIN NewValues N;

select * from #SecondTable;

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

A trigger would be more consistent than using a proc, unless you only want to do this for certain, specific INSERTs:

USE tempdb;

IF OBJECT_ID('dbo.FirstTable') IS NOT NULL
    DROP TABLE dbo.FirstTable;
GO
CREATE TABLE dbo.FirstTable ( Id INT IDENTITY(1,1), SName VARCHAR(20) NULL )
GO
IF OBJECT_ID('#SecondTable') IS NOT NULL
    DROP TABLE #SecondTable;
GO
CREATE TABLE #SecondTable ( Id INT IDENTITY (1,1), FirstId INT NOT NULL, SecondId INT NULL, ThirdId INT NULL, FourthId INT NULL )
INSERT INTO #SecondTable (FirstId, SecondId, ThirdId, FourthId) Select 100, 200, 300, 400
GO

CREATE TRIGGER FirstTable__TR_INS
ON dbo.FirstTable
AFTER INSERT 
AS
SET NOCOUNT ON;
UPDATE ST
SET FirstId = i.FirstId, SecondId = i.SecondId, ThirdId = i.ThirdId, FourthId = i.FourthId
FROM #SecondTable ST
CROSS APPLY (
    SELECT
        MAX(CASE WHEN row_num = 1 THEN i.Id END) AS FirstId,
        MAX(CASE WHEN row_num = 2 THEN i.Id END) AS SecondId,
        MAX(CASE WHEN row_num = 3 THEN i.Id END) AS ThirdId,
        MAX(CASE WHEN row_num = 4 THEN i.Id END) AS FourthId
    FROM (
        SELECT i.*, ROW_NUMBER() OVER(ORDER BY i.Id) AS row_num
        FROM inserted i
    ) AS i
) AS i
WHERE ST.Id = 1
GO

SELECT 'Before', * FROM #SecondTable;
INSERT INTO dbo.FirstTable ( SName ) VALUES('SName1'), ('SName1.'), ('SName1..'), ('SName1...');
SELECT * FROM dbo.FirstTable;
SELECT 'After', * FROM #SecondTable;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlislife Ten Centuries Points: 1251 More actions · Answer 3

Thanks for both of your responses. Much appreciated. I will try both of them and see what works best for my problem case.