1 ALTER PROCEDURE [dbo].[Proc_px_inportscore]2 --@IdentityNum nvarchar (),3 @PlanType nvarchar( -),--Training Category GUID4 @BanQGuid nvarchar( -)--Schedule GUID5 as6 begin7 8 9 Ten --Import Results One Insert intoPk_user (Rowguid,name,identitynum,age,sex,danweiname,titlecode,mobilephone,isenable,[Status]) A(--Insert to Person table condition: the person in the temporary table is not judged by the Identitynum - Select LOWER(NEWID()), Name,identitynum,age, ( CaseSex when 'male' Then 0 Else 1 End), Danweiname,zhiwu,mobilephone,'1','2' fromPx_inportscore - WHERE not exists(SelectPk_user. Identitynum fromPk_userWHEREPx_inportscore.identitynum=Pk_user. Identitynum) the ) - - Insert intoPx_baom (rowguid,name,userguid,loginid,danweiname,pxprograms,note) -(--INSERT into Entry form criteria: Insert the person from the temporary table successfully into the people list and these people do not exist in the registration form: Userguid of the rowguid registration form of the personnel table + Select LOWER(NEWID()), A.name,b.rowguid,a.identitynum,a.danweiname,'Geneva', Note - fromPx_inportscore a + JoinPk_user b onA.identitynum=B.identitynum A whereB.rowguid not inch(SelectUserguid fromPx_baom) at - - ) - - Insert intoPx_baomdetail (rowguid,itemguid,itemname,isdel,classguid,parentguid,isconfirm) -(--INSERT into the Registration sub-table condition: Insert the person successfully into the registration form and these people are not in the parentguid of the sub-table RowGuid in Select LOWER(NEWID()),@PlanType,"','0',@BanQGuid, C.rowguid,'0' - fromPx_inportscore a to JoinPk_user b onA.identitynum=B.identitynum + JoinPx_baom C onB.rowguid=C.userguid - whereC.rowguid not inch(SelectParentguid frompx_baomdetail) the * ) $ Panax Notoginseng - --Success Token the UpdateBSetB.flag=1 + fromPx_baomdetail A,px_inportscore bwhereA.classguid=@BanQGuid A the Declare @successc int + Declare @failc int - $ Select @successc= Count(1) fromPx_inportscorewhereFlag='1' $ Select @failc= Count(1) fromPx_inportscorewhere IsNull(Flag,0)<>1 - - Select @successc asSUCCESSC,@failc asFAILC the - Wuyi the - End Wu GO
View Code
The stored procedure inserts data into the staging table and then inserts it into a different table based on the criteria