SQLTeam.com | Weblogs | Forums

Create stored procedure for multipe sql queries

select * from (
select Partner from AFG_STAGING_HISTORY.dbo.BUT000 where convert(date,LoadDateTime)='2020-12-08'
except
select Partner from H_EngageCustomer
) a
except
select Partner from tmpbut000Missing ;

with CTEBut00
as
(
select * from (
select partner, persnumber, loaddatetime, RANK() over(partition by partner order by loaddatetime desc) slno
from AFG_STAGING_HISTORY.dbo.BUT000
) a where slno =1 and partner like '022%'
)

-- Loading But000
, CTE
as
(
select partner, persnumber, loaddatetime from CTEBut00 where partner in (
select PARTNER from CTEBut00
except
select Partner from H_EngageCustomer
)
)

insert into tmpbut000Missing (partner, persnumber, loaddatetime)
select partner, persnumber, loaddatetime from CTE

insert into [TempBut000missingAll] ([CLIENT],[PARTNER],[TYPE],[BPKIND],[BU_GROUP],[BPEXT],[BU_SORT1],[BU_SORT2],[SOURCE],[TITLE],[XDELE],[XBLCK]
,[AUGRP],[TITLE_LET],[BU_LOGSYS],[CONTACT],[NOT_RELEASED],[NOT_LG_COMPETENT],[PRINT_MODE],[BP_EEW_DUMMY],[NAME_ORG1],[NAME_ORG2],[NAME_ORG3],[NAME_ORG4]
,[LEGAL_ENTY],[IND_SECTOR],[LEGAL_ORG],[FOUND_DAT],[LIQUID_DAT],[LOCATION_1],[LOCATION_2],[LOCATION_3],[NAME_LAST],[NAME_FIRST],[NAME_LST2],[NAME_LAST2]
,[NAMEMIDDLE],[TITLE_ACA1],[TITLE_ACA2],[TITLE_ROYL],[PREFIX1],[PREFIX2],[NAME1_TEXT],[NICKNAME],[INITIALS],[NAMEFORMAT],[NAMCOUNTRY],[LANGU_CORR]
,[XSEXM],[XSEXF],[BIRTHPL],[MARST],[EMPLO],[JOBGR],[NATIO],[CNTAX],[CNDSC],[PERSNUMBER],[XSEXU],[XUBNAME],[BU_LANGU],[BIRTHDT],[DEATHDT],[PERNO]
,[CHILDREN],[MEM_HOUSE],[PARTGRPTYP],[NAME_GRP1],[NAME_GRP2],[MC_NAME1],[MC_NAME2],[CRUSR],[CRDAT],[CRTIM],[CHUSR],[CHDAT],[CHTIM],[PARTNER_GUID]
,[ADDRCOMM],[TD_SWITCH],[IS_ORG_CENTRE],[DB_KEY],[VALID_FROM],[VALID_TO],[XPCPT],[NATPERS],[PAR_REL],[BP_SORT],[KBANKS],[KBANKL],[DataMappingErrorTypeID]
,[FileExecMasterID],[LOBFileID],[Slno])

select [CLIENT],[PARTNER],[TYPE],[BPKIND],[BU_GROUP],[BPEXT],[BU_SORT1],[BU_SORT2],[SOURCE],[TITLE],[XDELE],[XBLCK]
,[AUGRP],[TITLE_LET],[BU_LOGSYS],[CONTACT],[NOT_RELEASED],[NOT_LG_COMPETENT],[PRINT_MODE],[BP_EEW_DUMMY],[NAME_ORG1],[NAME_ORG2],[NAME_ORG3],[NAME_ORG4]
,[LEGAL_ENTY],[IND_SECTOR],[LEGAL_ORG],[FOUND_DAT],[LIQUID_DAT],[LOCATION_1],[LOCATION_2],[LOCATION_3],[NAME_LAST],[NAME_FIRST],[NAME_LST2],[NAME_LAST2]
,[NAMEMIDDLE],[TITLE_ACA1],[TITLE_ACA2],[TITLE_ROYL],[PREFIX1],[PREFIX2],[NAME1_TEXT],[NICKNAME],[INITIALS],[NAMEFORMAT],[NAMCOUNTRY],[LANGU_CORR]
,[XSEXM],[XSEXF],[BIRTHPL],[MARST],[EMPLO],[JOBGR],[NATIO],[CNTAX],[CNDSC],[PERSNUMBER],[XSEXU],[XUBNAME],[BU_LANGU],[BIRTHDT],[DEATHDT],[PERNO]
,[CHILDREN],[MEM_HOUSE],[PARTGRPTYP],[NAME_GRP1],[NAME_GRP2],[MC_NAME1],[MC_NAME2],[CRUSR],[CRDAT],[CRTIM],[CHUSR],[CHDAT],[CHTIM],[PARTNER_GUID]
,[ADDRCOMM],[TD_SWITCH],[IS_ORG_CENTRE],[DB_KEY],[VALID_FROM],[VALID_TO],[XPCPT],[NATPERS],[PAR_REL],[BP_SORT],[KBANKS],[KBANKL],[DataMappingErrorTypeID]
,[FileExecMasterID],[LOBFileID],[Slno] from (
select rank() over(partition by b.partner order by b.loaddatetime desc) slno1, b.* from
AFG_STAGING_HISTORY.dbo.BUT000 b with (nolock)
inner join tmpbut000Missing c
on c.PARTNER= b.PARTNER
) a
where slno1=1 --and partner like '022%'

-- Loading But051
Insert into tmpbut051missing([CLIENT],[RELNR],[PARTNER1],[PARTNER2],[DATE_TO],[RELTYP],[XRF],[FNCTN],[PAFKT],[DPRTMNT],[ABTNR],[PAAUTH],[PAVIP]
,[PAREM],[TEL_NUMBER],[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[SMTP_ADDRESS],[REL_PER],[REL_AMO],[REL_CUR],[CALL_RULEID],[VISIT_RULEID]
,[CALL_GUID],[VISIT_GUID],[BP_EEW_BUT051],[BP_EEW_BUT051_SP])
select [CLIENT],[RELNR],[PARTNER1],[PARTNER2],[DATE_TO],[RELTYP],[XRF],[FNCTN],[PAFKT],[DPRTMNT],[ABTNR],[PAAUTH],[PAVIP]
,[PAREM],[TEL_NUMBER],[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[SMTP_ADDRESS],[REL_PER],[REL_AMO],[REL_CUR],[CALL_RULEID],[VISIT_RULEID]
,[CALL_GUID],[VISIT_GUID],[BP_EEW_BUT051],[BP_EEW_BUT051_SP] from (
select rank() over(partition by b51.partner1 order by b51.loaddatetime desc) slno1, b51.*
from AFG_STAGING_HISTORY.dbo.BUT051 b51
inner join tmpbut000Missing t
on t.partner= b51.PARTNER1
) a
where slno1 =1 --and partner1 like '022%'

-- BUT020
insert into tmpBut020Missing( [CLIENT],[PARTNER],[ADDRNUMBER],[XDFADR],[ADEXT],[NATION],[GUID],[MOVE_ADDR],[DATE_FROM]
,[ADDRESS_GUID],[ADDR_VALID_FROM],[ADDR_VALID_TO],[ADDR_MOVE_DATE])
select [CLIENT],[PARTNER],[ADDRNUMBER],[XDFADR],[ADEXT],[NATION],[GUID],[MOVE_ADDR],[DATE_FROM]
,[ADDRESS_GUID],[ADDR_VALID_FROM],[ADDR_VALID_TO],[ADDR_MOVE_DATE] from (
select rank() over(partition by b20.PARTNER order by b20.loaddatetime desc) slno1, b20.*
from AFG_STAGING_HISTORY.dbo.BUT020 b20
inner join tmpbut000Missing t
on t.partner= b20.PARTNER
) a
where slno1=1 --and PARTNER like '022%'
-- 14931

--ADCP Loading
-- ADCP for persnumber (But000)

Insert into tmpADCPMissing ([slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[NATION],[DATE_TO],[COMP_PERS],[SO_KEY],[DEPARTMENT]
,[FUNCTION],[BUILDING],[FLOOR],[ROOMNUMBER],[ID_CODE],[IH_MAIL],[SORT1],[SORT2],[SORT_PHN],[ALT_COMPNY],[DEFLT_COMM],[TEL_NUMBER]
,[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[FLAGCOMM2],[FLAGCOMM3],[FLAGCOMM4],[FLAGCOMM5],[FLAGCOMM6],[FLAGCOMM7],[FLAGCOMM8]
,[FLAGCOMM9],[FLAGCOMM10],[FLAGCOMM11],[FLAGCOMM12],[FLAGCOMM13],[ADCP_UUID],[UUID_BELATED],[ID_CATEGORY],[ADCP_ERR_STATUS]
,[XPCPT])

select [slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[NATION],[DATE_TO],[COMP_PERS],[SO_KEY],[DEPARTMENT]
,[FUNCTION],[BUILDING],[FLOOR],[ROOMNUMBER],[ID_CODE],[IH_MAIL],[SORT1],[SORT2],[SORT_PHN],[ALT_COMPNY],[DEFLT_COMM],[TEL_NUMBER]
,[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[FLAGCOMM2],[FLAGCOMM3],[FLAGCOMM4],[FLAGCOMM5],[FLAGCOMM6],[FLAGCOMM7],[FLAGCOMM8]
,[FLAGCOMM9],[FLAGCOMM10],[FLAGCOMM11],[FLAGCOMM12],[FLAGCOMM13],[ADCP_UUID],[UUID_BELATED],[ID_CATEGORY],[ADCP_ERR_STATUS]
,[XPCPT] from (
select rank() over(partition by cp.persnumber order by cp.loaddatetime desc) slno1, cp.*
from AFG_STAGING_HISTORY.dbo.ADCP cp
inner join tmpbut000Missing t
on t.persnumber= cp.PERSNUMBER
) a
where slno1=1 --and PERSNUMBER='0017384886'
--14188

-- ADCP for AddrrNumber (But020)
Insert into tmpADCPMissing(slno1,[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[NATION],[DATE_TO],[COMP_PERS],[SO_KEY],[DEPARTMENT]
,[FUNCTION],[BUILDING],[FLOOR],[ROOMNUMBER],[ID_CODE],[IH_MAIL],[SORT1],[SORT2],[SORT_PHN],[ALT_COMPNY],[DEFLT_COMM],[TEL_NUMBER]
,[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[FLAGCOMM2],[FLAGCOMM3],[FLAGCOMM4],[FLAGCOMM5],[FLAGCOMM6],[FLAGCOMM7]
,[FLAGCOMM8],[FLAGCOMM9],[FLAGCOMM10],[FLAGCOMM11],[FLAGCOMM12],[FLAGCOMM13],[ADCP_UUID],[UUID_BELATED],[ID_CATEGORY]
,[ADCP_ERR_STATUS],[XPCPT])

select slno2,a.[CLIENT],a.[ADDRNUMBER],a.[PERSNUMBER],a.[DATE_FROM],a.[NATION],a.[DATE_TO],a.[COMP_PERS],a.[SO_KEY],a.[DEPARTMENT]
,a.[FUNCTION],a.[BUILDING],a.[FLOOR],a.[ROOMNUMBER],a.[ID_CODE],a.[IH_MAIL],a.[SORT1],a.[SORT2],a.[SORT_PHN],a.[ALT_COMPNY],a.[DEFLT_COMM],a.[TEL_NUMBER]
,a.[TEL_EXTENS],a.[FAX_NUMBER],a.[FAX_EXTENS],a.[FLAGCOMM2],a.[FLAGCOMM3],a.[FLAGCOMM4],a.[FLAGCOMM5],a.[FLAGCOMM6],a.[FLAGCOMM7]
,a.[FLAGCOMM8],a.[FLAGCOMM9],a.[FLAGCOMM10],a.[FLAGCOMM11],a.[FLAGCOMM12],a.[FLAGCOMM13],a.[ADCP_UUID],a.[UUID_BELATED],a.[ID_CATEGORY]
,a.[ADCP_ERR_STATUS],a.[XPCPT] from (
select rank() over(partition by cp.addrnumber order by cp.loaddatetime desc) slno2, cp.*
from AFG_STAGING_HISTORY.dbo.ADCP cp
inner join tmpbut020Missing t
on t.addrnumber= cp.addrnumber
) a left outer join tmpADCPMissing tcp
on tcp.ADDRNUMBER = a.ADDRNUMBER
and tcp.PERSNUMBER= a.PERSNUMBER
where tcp.ADDRNUMBER is null
and tcp.PERSNUMBER is null
and slno2=1
--2

-- ADRC based on But020
Insert into tmpADRCMissing ([CLIENT],[ADDRNUMBER],[DATE_FROM],[NATION],[DATE_TO],[TITLE],[NAME1],[NAME2],[NAME3],[NAME4],[NAME_TEXT]
,[NAME_CO],[CITY1],[CITY2],[CITY_CODE],[CITYP_CODE],[HOME_CITY],[CITYH_CODE],[CHCKSTATUS],[REGIOGROUP],[POST_CODE1],[POST_CODE2]
,[POST_CODE3],[PCODE1_EXT],[PCODE2_EXT],[PCODE3_EXT],[PO_BOX],[DONT_USE_P],[PO_BOX_NUM],[PO_BOX_LOC],[CITY_CODE2],[PO_BOX_REG]
,[PO_BOX_CTY],[POSTALAREA],[TRANSPZONE],[STREET],[DONT_USE_S],[STREETCODE],[STREETABBR],[HOUSE_NUM1],[HOUSE_NUM2],[HOUSE_NUM3],[STR_SUPPL1]
,[STR_SUPPL2],[STR_SUPPL3],[LOCATION],[BUILDING],[FLOOR],[ROOMNUMBER],[COUNTRY],[LANGU],[REGION],[ADDR_GROUP],[FLAGGROUPS],[PERS_ADDR]
,[SORT1],[SORT2],[SORT_PHN],[DEFLT_COMM],[TEL_NUMBER],[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[FLAGCOMM2],[FLAGCOMM3],[FLAGCOMM4],[FLAGCOMM5]
,[FLAGCOMM6],[FLAGCOMM7],[FLAGCOMM8],[FLAGCOMM9],[FLAGCOMM10],[FLAGCOMM11],[FLAGCOMM12],[FLAGCOMM13],[ADDRORIGIN],[MC_NAME1],[MC_CITY1]
,[MC_STREET],[EXTENSION1],[EXTENSION2],[TIME_ZONE],[TAXJURCODE],[ADDRESS_ID],[LANGU_CREA],[ADRC_UUID],[UUID_BELATED],[ID_CATEGORY],[ADRC_ERR_STATUS]
,[PO_BOX_LOBBY],[DELI_SERV_TYPE],[DELI_SERV_NUMBER],[COUNTY_CODE],[COUNTY],[TOWNSHIP_CODE],[TOWNSHIP],[MC_COUNTY],[MC_TOWNSHIP]
,[XPCPT],[DUNS],[DUNSP4],[REMARK])

select [CLIENT],[ADDRNUMBER],[DATE_FROM],[NATION],[DATE_TO],[TITLE],[NAME1],[NAME2],[NAME3],[NAME4],[NAME_TEXT]
,[NAME_CO],[CITY1],[CITY2],[CITY_CODE],[CITYP_CODE],[HOME_CITY],[CITYH_CODE],[CHCKSTATUS],[REGIOGROUP],[POST_CODE1],[POST_CODE2]
,[POST_CODE3],[PCODE1_EXT],[PCODE2_EXT],[PCODE3_EXT],[PO_BOX],[DONT_USE_P],[PO_BOX_NUM],[PO_BOX_LOC],[CITY_CODE2],[PO_BOX_REG]
,[PO_BOX_CTY],[POSTALAREA],[TRANSPZONE],[STREET],[DONT_USE_S],[STREETCODE],[STREETABBR],[HOUSE_NUM1],[HOUSE_NUM2],[HOUSE_NUM3],[STR_SUPPL1]
,[STR_SUPPL2],[STR_SUPPL3],[LOCATION],[BUILDING],[FLOOR],[ROOMNUMBER],[COUNTRY],[LANGU],[REGION],[ADDR_GROUP],[FLAGGROUPS],[PERS_ADDR]
,[SORT1],[SORT2],[SORT_PHN],[DEFLT_COMM],[TEL_NUMBER],[TEL_EXTENS],[FAX_NUMBER],[FAX_EXTENS],[FLAGCOMM2],[FLAGCOMM3],[FLAGCOMM4],[FLAGCOMM5]
,[FLAGCOMM6],[FLAGCOMM7],[FLAGCOMM8],[FLAGCOMM9],[FLAGCOMM10],[FLAGCOMM11],[FLAGCOMM12],[FLAGCOMM13],[ADDRORIGIN],[MC_NAME1],[MC_CITY1]
,[MC_STREET],[EXTENSION1],[EXTENSION2],[TIME_ZONE],[TAXJURCODE],[ADDRESS_ID],[LANGU_CREA],[ADRC_UUID],[UUID_BELATED],[ID_CATEGORY],[ADRC_ERR_STATUS]
,[PO_BOX_LOBBY],[DELI_SERV_TYPE],[DELI_SERV_NUMBER],[COUNTY_CODE],[COUNTY],[TOWNSHIP_CODE],[TOWNSHIP],[MC_COUNTY],[MC_TOWNSHIP]
,[XPCPT],[DUNS],[DUNSP4],[REMARK]
from (
select rank() over(partition by CR.Addrnumber order by CR.loaddatetime desc) slno2, CR.*
from AFG_STAGING_HISTORY.dbo.ADRC CR
inner join tmpbut020Missing t
on t.Addrnumber= CR.Addrnumber
) a where slno2=1
--14886

--Truncate table tmpAdr2Missing
-- Loading ADR2
Insert into tmpAdr2Missing ([slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[COUNTRY],[FLGDEFAULT],[FLG_NOUSE]
,[HOME_FLAG],[TEL_NUMBER],[TEL_EXTENS],[TELNR_LONG],[TELNR_CALL],[DFT_RECEIV],[R3_USER],[VALID_FROM],[VALID_TO])
select [slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[COUNTRY],[FLGDEFAULT],[FLG_NOUSE]
,[HOME_FLAG],[TEL_NUMBER],[TEL_EXTENS],[TELNR_LONG],[TELNR_CALL],[DFT_RECEIV],[R3_USER],[VALID_FROM],[VALID_TO] from (
select rank() over(partition by cp.Addrnumber order by cp.loaddatetime desc) slno1, cp.*
from AFG_STAGING_HISTORY.dbo.ADR2 cp
inner join tmpbut020Missing t
on t.Addrnumber= cp.Addrnumber
) a
where slno1=1
--tmpAdr2Missing
--29581

Insert into tmpAdr2Missing ([slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[COUNTRY],[FLGDEFAULT],[FLG_NOUSE]
,[HOME_FLAG],[TEL_NUMBER],[TEL_EXTENS],[TELNR_LONG],[TELNR_CALL],[DFT_RECEIV],[R3_USER],[VALID_FROM],[VALID_TO])
select a.[slno2],a.[CLIENT],a.[ADDRNUMBER],a.[PERSNUMBER],a.[DATE_FROM],a.[CONSNUMBER],a.[COUNTRY],a.[FLGDEFAULT],a.[FLG_NOUSE]
,a.[HOME_FLAG],a.[TEL_NUMBER],a.[TEL_EXTENS],a.[TELNR_LONG],a.[TELNR_CALL],a.[DFT_RECEIV],a.[R3_USER],a.[VALID_FROM],a.[VALID_TO]
from (
select rank() over(partition by cp.persnumber order by cp.loaddatetime desc) slno2, cp.*
from AFG_STAGING_HISTORY.dbo.ADR2 cp
inner join tmpbut000Missing t
on t.persnumber= cp.PERSNUMBER
) a left outer join tmpAdr2Missing tcp
on tcp.ADDRNUMBER = a.ADDRNUMBER
and tcp.PERSNUMBER= a.PERSNUMBER
where tcp.ADDRNUMBER is null
and tcp.PERSNUMBER is null
and slno2=1
-- 295 Records

-- Loading ADR6
Insert into tmpAdr6Missing([slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[FLGDEFAULT],[FLG_NOUSE],[HOME_FLAG],[SMTP_ADDR]
,[SMTP_SRCH],[DFT_RECEIV],[R3_USER],[ENCODE],[TNEF],[VALID_FROM],[VALID_TO])
select [slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[FLGDEFAULT],[FLG_NOUSE],[HOME_FLAG],[SMTP_ADDR]
,[SMTP_SRCH],[DFT_RECEIV],[R3_USER],[ENCODE],[TNEF],[VALID_FROM],[VALID_TO] from (
select rank() over(partition by cp.Addrnumber order by cp.loaddatetime desc) slno1, cp.*
from AFG_STAGING_HISTORY.dbo.ADR6 cp
inner join tmpbut020Missing t
on t.Addrnumber= cp.Addrnumber
) a
where slno1=1
--8316

Insert into tmpAdr6Missing([slno1],[CLIENT],[ADDRNUMBER],[PERSNUMBER],[DATE_FROM],[CONSNUMBER],[FLGDEFAULT],[FLG_NOUSE],[HOME_FLAG],[SMTP_ADDR]
,[SMTP_SRCH],[DFT_RECEIV],[R3_USER],[ENCODE],[TNEF],[VALID_FROM],[VALID_TO])
select a.[slno2],a.[CLIENT],a.[ADDRNUMBER],a.[PERSNUMBER],a.[DATE_FROM],a.[CONSNUMBER],a.[FLGDEFAULT],a.[FLG_NOUSE],a.[HOME_FLAG],a.[SMTP_ADDR]
,a.[SMTP_SRCH],a.[DFT_RECEIV],a.[R3_USER],a.[ENCODE],a.[TNEF],a.[VALID_FROM],a.[VALID_TO] from (
select rank() over(partition by cp.persnumber order by cp.loaddatetime desc) slno2, cp.*
from AFG_STAGING_HISTORY.dbo.ADR6 cp
inner join tmpbut000Missing t
on t.persnumber= cp.PERSNUMBER
) a left outer join tmpAdr6Missing tcp
on tcp.ADDRNUMBER = a.ADDRNUMBER
and tcp.PERSNUMBER= a.PERSNUMBER
where tcp.ADDRNUMBER is null
and tcp.PERSNUMBER is null
and slno2=1
-- 0 Records

create sp .. syntax .. put select queries in it

create blah_sp
begin
all selects
end

what do you need help with ? you dont know anything about stored prcoedures

Hi I have multiple insert and select statements the insert tables are not created in DB so how can i create in SP and

you can use temp tables # if it does not allow you to create tables in Stored Procedure

need to check the data after the procedure creation so i have to create normal tables , i have created normal tables with insert and select statement , im able to execute the sp but not sure whether my results are right or wrong, if you can check i can share the procedure.

one
way to check is

run the select seperately and then compare that to the results of the insert in your table

you can use EXCEPT .. OR joins .. OR Random Spot Checks

there is third party software that can do that for you also like Red Gate ( Paid) or Open Source Software

Please share your procedure

I think my limit is exceeded if you can share your email id i can send it

Nope, not sharing email id. Please post it here, we will help you. This is a no limit zone

i am sharing my email id .. NAUMON765@gmail.com
please send to that

but i dont understand where you are having the issue

You dont understand the code OR
is it toooooo loong
or
you dont have any experience in SQL

hopefully .. North Korea wont hack my Whole Computer , Network .. keeping fingers crossed
i only have two thousand in my bank account
:rofl: :rofl: :stuck_out_tongue_winking_eye: :stuck_out_tongue_winking_eye: