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.