UPDATE with result from INSERT query

user_14009974 0
Viewed 87

I have an existing table, A, defined as follows:

CREATE TABLE dbo.A(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    name nvarchar(255) NOT NULL UNIQUE NONCLUSTERED
);

This table has 1,000s of existing records.

I now require a new table, B, defined as follows:

CREATE TABLE dbo.B(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    some_value int NOT NULL
);

I need to create a record in B for each record in A and add a reference from the A record to the corresponding B record. Firstly, I need to add a new field to table A that references table B (which necessarily must be nullable with a default value of NULL, at this stage):

ALTER TABLE dbo.A
ADD b_id int NULL references dbo.B;

How can I create the necessary B records and update the A.b_id field accordingly? I'm looking for something like this:

UPDATE dbo.A
SET b_id = (INSERT INTO dbo.B(some_value) VALUES(5));

such that the value of b_id is the id field of the newly inserted B. (Clearly, this query isn't valid.)

The only solution I can come up with is to write a complex stored procedure using multiple, separate queries. Is there a way to do this with a single query?

tsql sql-server
2 Answers

IF I am understanding correctly, there's no need for a CURSOR here. What you could do is a MERGE that allows your to OUTPUT both columns from the source data and the target:

CREATE TABLE dbo.A(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    name nvarchar(255) NOT NULL UNIQUE NONCLUSTERED
);
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N))
INSERT INTO dbo.A
SELECT NEWID()
FROM N N1, N N2, N N3;
GO

CREATE TABLE dbo.B(
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
    some_value int NOT NULL
);
GO
ALTER TABLE dbo.A
ADD b_id int NULL references dbo.B;
GO

DECLARE @IDs table (a_id int, b_id int)

MERGE dbo.B B
USING dbo.A A ON B.id = A.b_id
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (some_value)
    VALUES(RAND() * 1000)
OUTPUT A.id, inserted.id
INTO @IDs;

UPDATE A
SET b_id = I.b_id
FROM dbo.A A
     JOIN @Ids I ON A.id = I.a_id;
GO

SELECT *
FROM dbo.A;

GO

DROP TABLE dbo.A;
DROP TABLE dbo.B;

db<>fiddle

user_20299830

If you are willing (and able) to make a slight adjustment to TableB as well, you can use OUTPUT when inserting. I added a_id to TableB for my example. Updating b_id back to TableA is a bit redundant as you would now have TableA's ID in TableB for joining.

Here's an example.

-- Mock tables.
DECLARE @TableA table ( id int IDENTITY (100,1) PRIMARY KEY, [name] nvarchar(255), b_id int );
DECLARE @TableB table ( id int IDENTITY (1,1) PRIMARY KEY, a_id int, some_value int );

-- Mock data.
INSERT INTO @TableA ( [name] ) VALUES
    ( 'TableA_1' ), ( 'TableA_2' ), ( 'TableA_3' );

-- Create an OUTPUT table to capture the values inserted into TableB.
DECLARE @out table ( a_id int, b_id int );

INSERT INTO @TableB ( a_id, some_value )
OUTPUT inserted.a_id, inserted.id INTO @out
SELECT
    id AS a_id, 999 AS some_value
FROM @TableA;

-- Update TableA's b_id column.

UPDATE @TableA
SET 
    b_id = o.b_id
FROM @TableA AS a
INNER JOIN @out AS o
    ON a.id = o.a_id;

-- Show updated results.
SELECT * FROM @TableA ORDER BY id;

Returns

+-----+----------+------+
| id  |   name   | b_id |
+-----+----------+------+
| 100 | TableA_1 |    1 |
| 101 | TableA_2 |    2 |
| 102 | TableA_3 |    3 |
+-----+----------+------+
user_33913340
Related