re = /(?<i_cl>insert (?: \s (?: into | bulk ))?) \s
(?(?=openquery) (?:
(?<i_oq>openquery) \s \(
(?<i_oq_srv>\S+) \, \s \'
.*?
\'\)
)
| (?:
(?<i_tbl>\S+)
)
)
.*? \s select \s .*?
(?:
(?<f_cl>from) \s
(?(?=openquery) (?:
(?<f_oq>openquery) \s \(
(?<f_oq_srv>\S+) \, \s \'
(?: .+? (?<f_oq_cl>from|join) \s (?<f_oq_tbl>\S+) )
.* \'\)
)
| (?: (?<f_tbl>\S+) )
)
)+/mix
str = '
insert openquery (datadata_adm, \'SELECT MOTONADPIS_ID, CALCDATE, POPA_ID, POPA_BUMP, PICT_ID, PICT_BUMP, KOL_NADPIS, OST_PERIOD_EXPIRE, FERMATA_DD, FERMATA_HM, FERMATA_DK, MAX_SHT_VSE_MX, MAX_SHT_3_BLOCK_MX, OPRONUDITELNO, DAYS_COUNT, DECOCIATOR, USERNAME, Name_p_ROSP, Tip_rascheta, ID_PIGISTER FROM ZOLA.T_MOTONADPIS_PERMALATE\') SELECT t.[MOTONADPIS_ID], cast ([CALCDATE] as date), c.T_POPA_ID, [POPA_BUMP], a.t_PICT_id, [PICT_BUMP], [KOL_NADPIS], case when [OST_PERIOD_EXPIRE]=\'\' then null else [OST_PERIOD_EXPIRE] end as [OST_PERIOD_EXPIRE], [FERMATA_DD], [FERMATA_HM], [FERMATA_DK], [MAX_SHT_VSE_MX], [MAX_SHT_3_BLOCK_MX], [OPRONUDITELNO], [DAYS_COUNT], [DECOCIATOR], [USERNAME], [Name_p_ROSP], [Tip_rascheta], w.ID_PIGISTER FROM [ZOLA].[bodo].[T_MOTONADPIS_PERMAlate_N] t join SOURCE..T_POPA c on c.O_POPA_BUMP=t.POPA_BUMP join SOURCE..t_PICT a on a.T_PICT_BUMP=t.PICT_BUMP left join ZOLA..T_MOTONADPIS_Web_Document_Links w on w.MOTONADPIS_ID=t.MOTONADPIS_ID where t.[MOTONADPIS_ID]=@MOTORO
insert bulk dbVHS..T_GIST select t2.ClDECOqueIDRow_AL, t2.ID_CUMMI, t2.ID_PIGISTER, t2.RN, t2.IS_TRANSMITTED, t1.STATUS_ID, t1.ErrorMSG, t2.USERNAME, t2.ID_POPA, t2.ID_WS, t2.ID_PICT, t2.ID_GRP_PICT_LVL0, t2.ID_GRP_PICT_LVL1, t2.ID_GRP_PICT_LVL2, t2.ID_GRP_PICT_LVL3, t2.ASTNAME, t2.QUANT, t2.PERC, t2.BDATE, t2.EDATE, t2.PICTSET_TYPE, t2.FUCKTION, t2.DESCRIPT, t2.ID_ADAPTER, getdate () as END_TIME from ..T_AST_CUMMI_WORK_RESULT t1 join ..T_PROCESS_PERMA t2 on t1.ClDECOqueIDRow = t2.ClDECOqueIDRow_AL left join ..T_GIST t3 on t1.ClDECOqueIDRow = t3.ClDECOqueIDRow_AL and t2.ID_CUMMI = t3.ID_CUMMI where t3.ClDECOqueIDRow_AL is null
insert into ..T_GIST (v1, v2, v3) select t2.ClDECOqueIDRow_AL, t2.ID_CUMMI, t2.ID_PIGISTER, t2.RN, t2.IS_TRANSMITTED, t1.STATUS_ID, t1.ErrorMSG, t2.USERNAME, t2.ID_POPA, t2.ID_WS, t2.ID_PICT, t2.ID_GRP_PICT_LVL0, t2.ID_GRP_PICT_LVL1, t2.ID_GRP_PICT_LVL2, t2.ID_GRP_PICT_LVL3, t2.ASTNAME, t2.QUANT, t2.PERC, t2.BDATE, t2.EDATE, t2.PICTSET_TYPE, t2.FUCKTION, t2.DESCRIPT, t2.ID_ADAPTER, getdate () as END_TIME from ..T_AST_CUMMI_WORK_RESULT t1, Cross..join tc join ..T_PROCESS_PERMA t2 on t1.ClDECOqueIDRow = t2.ClDECOqueIDRow_AL left join ..T_GIST t3 on t1.ClDECOqueIDRow = t3.ClDECOqueIDRow_AL and t2.ID_CUMMI = t3.ID_CUMMI where t3.ClDECOqueIDRow_AL is null
insert into ..T_LOH_2 select getdate (), ID_CUMMI, 7, count (1), 11, count (case when PROCESS_STATUS = 2 then 1 else null end), ID_PIGISTER, USERNAME from #TMP_DELETE group by ID_CUMMI, ID_PIGISTER, USERNAME
insert into ..T_GIST.[zjopa] select t2.ClDECOqueIDRow_AL, t2.ID_CUMMI, t2.ID_PIGISTER, t2.RN, t2.IS_TRANSMITTED, t1.STATUS_ID, t1.ErrorMSG, t2.USERNAME, t2.ID_POPA, t2.ID_WS, t2.ID_PICT, t2.ID_GRP_PICT_LVL0, t2.ID_GRP_PICT_LVL1, t2.ID_GRP_PICT_LVL2, t2.ID_GRP_PICT_LVL3, t2.ASTNAME, t2.QUANT, t2.PERC, t2.BDATE, t2.EDATE, t2.PICTSET_TYPE, t2.FUCKTION, t2.DESCRIPT, t2.ID_ADAPTER, getdate () as END_TIME from ..T_AST_CUMMI_WORK_RESULT join ..T_PROCESS_PERMA t2 on t1.ClDECOqueIDRow = t2.ClDECOqueIDRow_AL left join ..T_GIST t3 on t1.ClDECOqueIDRow = t3.ClDECOqueIDRow_AL and t2.ID_CUMMI = t3.ID_CUMMI where t3.ClDECOqueIDRow_AL is null
insert into ..T_GIST select t2.ClDECOqueIDRow_AL, t2.ID_CUMMI, t2.ID_PIGISTER, t2.RN, t2.IS_TRANSMITTED, t1.STATUS_ID, t1.ErrorMSG, t2.USERNAME, t2.ID_POPA, t2.ID_WS, t2.ID_PICT, t2.ID_GRP_PICT_LVL0, t2.ID_GRP_PICT_LVL1, t2.ID_GRP_PICT_LVL2, t2.ID_GRP_PICT_LVL3, t2.ASTNAME, t2.QUANT, t2.PERC, t2.BDATE, t2.EDATE, t2.PICTSET_TYPE, t2.FUCKTION, t2.DESCRIPT, t2.ID_ADAPTER, getdate () as END_TIME from openquery (tera_serv, \'select from ..T_AST_CUMMI_WORK_RESULT t1 join ..T_PROCESS_PERMA t2 on t1.ClDECOqueIDRow = t2.ClDECOqueIDRow_AL left join ..T_GIST t3 on t1.ClDECOqueIDRow = t3.ClDECOqueIDRow_AL and t2.ID_CUMMI = t3.ID_CUMMI where t3.ClDECOqueIDRow_AL is null\') join abc..abd'
# Print the match result
str.scan(re) do |match|
puts match.to_s
end
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for Ruby, please visit: http://ruby-doc.org/core-2.2.0/Regexp.html