0

I am trying to insert data to persons table. Parallely I have to copy the inserted person id (identity column value) along with providerid from someother table to providerperson. I tried the below query but I got an error.

The multi-part identifier "P.Provider_ID" could not be bound.

INSERT INTO PERSONS (
            FirstName
            ,LastName
            ,Gender
            ,DOB
            ,EmailID
            ,OfficePhone
            ,Fax
            ,Mobile
            ,CertificationType_ID
            --,License
            --,StateCertificaionNo
            --,StateCertificationExpiration
            --,Comments
            ,ERTCleared
            ,IsAMR
            ,IsActive
            ,CreatedDate
            ,ModifiedDate
            ,IsCrewMember
            ,UserName
            )
        OUTPUT INSERTED.Person_ID
            ,P.Provider_ID
        --,getdate()
        --,getdate()
        --,@appuserId
        --,@appuserId
        INTO @ProviderPersons(PersonID, ProviderID)
        --00, CreatedDate, ModifiedDate, CreatedUser_ID, ModifiedUser_ID)
        SELECT S.FirstName
            ,S.LastName
            ,S.Gender
            ,S.DOB
            ,S.EmailID
            ,S.OfficePhone
            ,S.Fax
            ,S.Mobile
            ,1 AS CertificationType_ID
            ,0 AS ERTCleared
            ,CASE 
                WHEN S.IsAMR = 'Yes'
                    THEN 1
                ELSE 0
                END AS IsAMR
            ,1 AS IsActive
            ,getdate() AS CreatedDate
            ,getdate() AS ModifiedDate
            ,1 AS IsCrewMember
            ,S.UserName
        FROM [dbo].[STA_CrewMembers] S
        INNER JOIN [dbo].[Providers] P ON P.BusinessUnitNumber = S.BusinessUnitNumber
            OR P.NAME = S.PROVIDER
        WHERE NOT EXISTS (
                SELECT 1
                FROM Persons
                WHERE EmailID = S.EmailID
                )

Please suggest me how to handle this in a better way. Thanks in Advance.

StackUser
  • 5,370
  • 2
  • 24
  • 44

1 Answers1

1

use 

OUTPUT INSERTED.Person_ID
        ,INSERTED.Provider_ID

you can't use table alias..Example below won't work due to table alias

declare @id table
(
id int
)

insert into t1
output n.id into @id
select top 10* from numbers n


select * from @id
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94