Actual Practice : Insert into select from in my work -5

In my present work of testing, SQL statements are the most usual of all the testing process. I must practise it as much as I can. All I write are a must skill of Testing. Now let me show them and this will be a memo in case I would forget it one day.

Insert into Table1(column1,column2,column3,.......) select column1,column2,column3,....... from Table2 where......

Here is a true example :

insert into Process.dbo.HmpFinalModAgreementLetterData(lossmitigationdealid,loannumberkey,loan_no,hmpmodificationdataid,exclusionreason,trialmodid)
select top 1 hmd.hmplossmitigationdealid
,convert(bigint,hmd.servicerloannumber) loannumberkey
,hmd.servicerloannumber loan_no
,hmd.hmpmodificationdataid 
,null exculsionreason
,(
    SELECT TOP 1 HMPMODIFICATIONDATAID FROM HMPMODIFICATIONDATA 
    WHERE SUBMISSIONTYPE='Trial' 
    --and  SERVICERLOANNUMBER= hmd.servicerloannumber
    order by HMPMODIFICATIONDATAID desc
  ) trialmodid
from hmpmodificationdata hmd 
where exists (
      select 1 from hmpmodificationdata hmd1            
      inner join lossmitigationdeal lmd on hmd1.hmplossmitigationdealid = lmd.LossMitigationDealId  
      INNER JOIN LossMitigationDealStatusHistory  SH ON LMD.LossMitigationDealId = SH.LossMitigationDealId 
      where SH.LossMitigationDealStatusID = 7 --'Defaulted' 
      ..........
      And lmd.loannumberkey=convert(bigint,hmd.servicerloannumber)
      ) 
and hmd.submissiontype='final' 
order by hmd.hmpmodificationdataid desc

Here is our attention :
a. Table1 must be an existing table.

b. We can insert a constant into Table1.
Let us have look at the script : There is no “exculsionreason” in Table2. Then we select “NULL” as exculsionreason to insert into column “exculsionreason” of Table1

c. We can select a new column from some other tables as the value of Table1 column
Let us analysis the script :

(
SELECT TOP 1 HMPMODIFICATIONDATAID FROM HMPMODIFICATIONDATA 
    WHERE SUBMISSIONTYPE='Trial' 
    --and  SERVICERLOANNUMBER= hmd.servicerloannumber
    order by HMPMODIFICATIONDATAID desc
 ) trialmodid

The “trialmodid” is selected as the column “trialmodid” of Table1.