How to use output variable in insert query

Question

I am inserting records from our crm to our erp. The phone table uses a identity column from the people table. I need to insert the people record and capture the value in the PersonId which is an identity column and then use that PersonId as the key to insert records to the Phone table. I get the error: Msg 137, Level 16, State 1, Line 16 Must declare the scalar variable "@IdentityID". Msg 137, Level 16, State 1, Line 17 Must declare the scalar variable "@IdentityID".

--IdentityTable
CREATE TABLE [dbo].[People](
    [People_ID] [int] NOT NULL,
    [text] [nvarchar](50) NULL,
    [PersonId] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

--Phone
CREATE TABLE [dbo].[Phone](
    [PersonId] [int] NOT NULL,
    [text] [nvarchar](50) NULL,
    [Number] [nchar](10) NULL
) ON [PRIMARY]


declare @IdentityID table (PersonId int);

INSERT INTO [Bridge].[dbo].[People]
           ([People_ID]
           ,[text])
           output Inserted.PersonId into @IdentityID
     VALUES
           (3,'row1'),
           (4,'row2');

INSERT INTO [Bridge].[dbo].[Phone]
           (PersonId
           ,[text]
           ,[Number])
     VALUES
           (@IdentityID,'row1'),
           (@IdentityID,'row2');     

Print 'IdentityID' + @IdentityID

Msg 137, Level 16, State 1, Line 16 Must declare the scalar variable "@IdentityID". Msg 137, Level 16, State 1, Line 17 Must declare the scalar variable "@IdentityID".

Kannan Kandasamy · Accepted Answer · 2017-05-23 15:53:15Z

1

Output will be to tablevariable you can use as below:

declare @IdentityID table (PersonId int);

INSERT INTO [dbo].[People]
           ([People_ID]
           ,[text])
           output Inserted.PersonId into @IdentityID(PersonId)
     VALUES
           (3,'row1'),
           (4,'row2');

INSERT INTO [dbo].[Phone]
           (PersonId
           ,[text]
           ,[Number])
          select PersonId,'row1', 1 from @IdentityID
          union all select PersonId,'row2', 2 from @IdentityID

select * from @IdentityID

answered May 23, 2017 at 15:53

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use output variable in insert query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related