transact sql - can't select from variable-CodePudding

I'm trying to take the ID of a new record inserted into TABLE A and use it in a subsequent insert.

But I'm getting an error saying that the newUserId variable isn't declared. it's actually a table variable.

The code looks like this;

USE Acme;
GO
DECLARE     @userPrincipalName VARCHAR(100),
            @displayName VARCHAR(100),
            @domainName VARCHAR(100),
            @tId INT,
            @uname VARCHAR(100);
DECLARE     @newUserid TABLE (
            id INT
            );
    
-- FILL ME IN
SET @domainName = 'mydomain.org';
SET @userPrincipalName = '[email protected]';
SET @displayName = 'Pan, Peter';
SET @tId=4;
SET @uname = 'ppan';

INSERT INTO dbo.User
            (column list)
OUTPUT Inserted.ID INTO @newUserId

SELECT '', @domainName, getutcdate(), @userPrincipalName, @displayName, other fields

-- Create New Profile Using NewID ** THIS IS WHERE IT DIES
INSERT INTO dbo.UserProfile
    SELECT @newUserId.id, 
    '{}', GETDATE(), getdate(), ''

The specific error is:

8:55:51 AMStarted executing query at Line 1
Commands completed successfully.
8:55:51 AMStarted executing query at Line 3
Msg 137, Level 16, State 1, Line 36
Must declare the scalar variable "@newUserid".
Total execution time: 00:00:00.017

I've abbreviated the code for the sake of this post but line 36 is where I'm referencing SELECT @newUserId.id

Any tips would be appreciated.

Thanks

CodePudding user response：

Because @newUserId is a table variable you can't select it as a variable.

you can try to use INSERT INTO ....SELECT ... FROM

INSERT INTO dbo.UserProfile
SELECT id, '{}', GETDATE(), GETDATE(), ''
FROM @newUserId