Sean is right, you're overcomplicating things. You can use INSERT INTO...SELECT instead of values.

INSERT INTO dbo.HemTransfer (

TraTrtId

,TraAniId

,TraDate

)

SELECT 2

,ha.AniId

,ha.AniBirthday

FROM HemAnimal ha --This is a guess

WHERE ha.AniUserNumber = 123456