regex101: PCRE (Legacy) Match Workspace

Group i_cl(?<i_cl>insert (?: \s (?: into | bulk ))?)

insert the literal text insert (case insensitive)

Non-Capturing Group(?: \s (?: into | bulk ))?

?repeats the group contents below at most once:

\sany whitespace character

Non-Capturing Group(?: into | bulk )

1st Alternative into

into the literal text into (case insensitive)

2nd Alternative bulk

bulk the literal text bulk (case insensitive)

\sany whitespace character

Condition(?(?=openquery) (?: (?<i_oq>openquery) \s $ (?<i_oq_srv>\S+) \, \s \' .*? \'$ ) | (?: (?<i_tbl>\S+) ) )

Uses the first branch if the lookaround matches

Positive Lookahead(?=openquery)

openquerythe literal text openquery (case insensitive)

If the condition is true, match this branch: (?: (?<i_oq>openquery) \s $ (?<i_oq_srv>\S+) \, \s \' .*? \'$ )

Non-Capturing Group(?: (?<i_oq>openquery) \s $ (?<i_oq_srv>\S+) \, \s \' .*? \'$ )

Group i_oq(?<i_oq>openquery)

openquerythe literal text openquery (case insensitive)

\sany whitespace character

\(the literal ( (40₁₀ / 50₈ / 28₁₆)

Group i_oq_srv(?<i_oq_srv>\S+)

\S+any non-whitespace character, repeated at least once

\,the literal , (44₁₀ / 54₈ / 2C₁₆)

\sany whitespace character

\'the literal ' (39₁₀ / 47₈ / 27₁₆)

.*?any character (except for line terminators), repeated any number of times

\'the literal ' (39₁₀ / 47₈ / 27₁₆)

\)the literal ) (41₁₀ / 51₈ / 29₁₆)

Otherwise, match this branch: (?: (?<i_tbl>\S+) )

Non-Capturing Group(?: (?<i_tbl>\S+) )

Group i_tbl(?<i_tbl>\S+)

\S+any non-whitespace character, repeated at least once

.*?any character (except for line terminators), repeated any number of times

\sany whitespace character

select the literal text select (case insensitive)

\sany whitespace character

.*?any character (except for line terminators), repeated any number of times

Non-Capturing Group(?: (?<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+) ) ) )+

+repeats the group contents below at least once:

Group f_cl(?<f_cl>from)

fromthe literal text from (case insensitive)

\sany whitespace character

Condition(?(?=openquery) (?: (?<f_oq>openquery) \s $ (?<f_oq_srv>\S+) \, \s \' (?: .+? (?<f_oq_cl>from|join) \s (?<f_oq_tbl>\S+) ) .* \'$ ) | (?: (?<f_tbl>\S+) ) )

Uses the first branch if the lookaround matches

Positive Lookahead(?=openquery)

openquerythe literal text openquery (case insensitive)

If the condition is true, match this branch: (?: (?<f_oq>openquery) \s $ (?<f_oq_srv>\S+) \, \s \' (?: .+? (?<f_oq_cl>from|join) \s (?<f_oq_tbl>\S+) ) .* \'$ )

Non-Capturing Group(?: (?<f_oq>openquery) \s $ (?<f_oq_srv>\S+) \, \s \' (?: .+? (?<f_oq_cl>from|join) \s (?<f_oq_tbl>\S+) ) .* \'$ )

Group f_oq(?<f_oq>openquery)

openquerythe literal text openquery (case insensitive)

\sany whitespace character

\(the literal ( (40₁₀ / 50₈ / 28₁₆)

Group f_oq_srv(?<f_oq_srv>\S+)

\S+any non-whitespace character, repeated at least once

\,the literal , (44₁₀ / 54₈ / 2C₁₆)

\sany whitespace character

\'the literal ' (39₁₀ / 47₈ / 27₁₆)

Non-Capturing Group(?: .+? (?<f_oq_cl>from|join) \s (?<f_oq_tbl>\S+) )

.+?any character (except for line terminators), repeated at least once

Group f_oq_cl(?<f_oq_cl>from|join)

1st Alternativefrom

fromthe literal text from (case insensitive)

2nd Alternativejoin

\sany whitespace character

Group f_oq_tbl(?<f_oq_tbl>\S+)

.*any character (except for line terminators), repeated any number of times

\'the literal ' (39₁₀ / 47₈ / 27₁₆)

\)the literal ) (41₁₀ / 51₈ / 29₁₆)

Otherwise, match this branch: (?: (?<f_tbl>\S+) )

Global pattern flags

g modifier: global. Finds all matches instead of stopping after the first

m modifier: multiline. Causes ^ and $ to match the start and end of each line, not only the start and end of the string

i modifier: insensitive. Matches without distinguishing uppercase and lowercase letters

x modifier: extended. Ignores unescaped whitespace and comments outside character classes

Search reference

Regular Expression

(?<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+) )
)
)+

New


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

Regular Expressions 101

Regular Expression

Substitution