I have a couple of tables that are joined: Table A ([Id], [Name], [Active]) Table B ([Id], [Name], [A_Id])

I would like to insert into Table A first, then use the Id of the new row to insert into Table B.

DECLARE @NEWID table(NewID Numeric(19, 0));

INSERT [dbo].[TableA] ([Name], [Active]) 
OUTPUT INSERTED.Id INTO @NEWID (NewID)
VALUES ('testtesttest', 1);

INSERT [dbo].[TableB] ([Name], [A_Id])
VALUES ('name for b', ??? @NEWID.NewID ???)

I can OUTPUT the id into a temporary table but how do I use this in the next insert statement?

CodePudding user response：

The problem that you're running into is that the target of the OUTPUT clause needs to be a table-like thing (in this case, you're using a table variable which works), but the values clause needs a scalar. So you'll need to introduce a scalar variable into the mix.

DECLARE @NEWID table(NewID Numeric(19, 0));
DECLARE @NEWIDValue Numeric(19, 0);

INSERT [dbo].[TableA] ([Name], [Active]) 
OUTPUT INSERTED.Id INTO @NEWID (NewID)
VALUES ('testtesttest', 1);

set @NEWIDValue = (select NewID from @NEWID);

INSERT [dbo].[TableB] ([Name], [B_Id])
VALUES ('name for b',@NEWIDValue);

Alternatively, and fancier (read may or may not work based on your use case) is to use the OUTPUT clause from the first insert to insert into the second table directly

INSERT [dbo].[TableA] ([Name], [Active]) 
OUTPUT INSERTED.Id, 'name for b' INTO [dbo].[TableB] ([B_Id], [Name])
VALUES ('testtesttest', 1);

CodePudding user response：

the user defined variable is @NEWID, which isn ba table and that gets filled so use it as you wpould use any other table, where you need to get onoly one Value

CREATE TABLE tableA  
(  
 ID int IDENTITY(1,1),
   [Name] varchar (200), [Active] int
);  

CREATE TABLE tableB  
(  
 ID int IDENTITY(1,1),
   [Name] varchar (200), [A_ID] int
);  

    DECLARE @NEWID table(NewID BIGINT);
    
    INSERT [dbo].[TableA] ([Name], [Active]) 
    OUTPUT INSERTED.Id INTO @NEWID (NewID)
    VALUES ('testtesttest', 1);
    
    INSERT [dbo].[TableB] ([Name], [A_Id])
    VALUES ('name for b', (SELECT TOP 1 NewID FROM @NEWID ORDER BY NewID DESC))

2 rows affected

SELECT * FROM tableA

SELECT * FROM tableB

fiddle