Need help to solve this scenario

ETL vs ELTL 26 Reputation points

Hi All,

I need to write a recursive code but can't find the solution yet.


  1. There is a table (ERPCustomer) that has 2 columns ERPCustomerBK, SF_Account
  2. Sample Values
    ERPCustomer_BK, SF_AccountID
    AXPT5|75388|US-CA 0011400001a9G6IAAU
    AXPT5|74334|US-CA 001a000001BCKyXAAX

I need to find out the SF_AccountID for 1 ERPCustomer_BK.
"AXPT5|75388|US-CA" BK I have 2 SF_Account in my table "0011400001a9G6IAAU", "001a000001BCKyXAAX"

Now I need to check for these 2 SF_AccountID do we have any other ERPCustomer_BK in the data

This should go upto no further data found either for ERPCustomer_BK OR SF_AccountID.


A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP

    As Ronen said, you went a little overboard that with the test data. For most questions of this type we rarely need more than 20 rows.

    Anyway, here is a query, which may do what you are looking for. This is a recursive CTE, and the extra column datastr serves to prune combinations we have already found.

    WITH rekurs AS (
       SELECT erpcustomer_bk, sf_accountID, 
              cast(erpcustomer_bk + ' ' + sf_accountID as nvarchar(MAX)) AS datastr
       FROM   temp.Erpx 
       WHERE  erpcustomer_bk = '5|75388|US-CA' 
       UNION ALL
       SELECT E.erpcustomer_bk, E.sf_accountID, 
              r.datastr + '///' + E.erpcustomer_bk + ' ' + E.sf_accountID
       FROM   rekurs r
       JOIN   temp.Erpx E ON E.erpcustomer_bk = r.erpcustomer_bk OR
                             E.sf_accountID   = r.sf_accountID
       WHERE  charindex(E.erpcustomer_bk + ' ' + E.sf_accountID, r.datastr) = 0
    SELECT erpcustomer_bk, sf_accountID
    FROM  rekurs

    I am not sure that this is the most efficient solution. Maybe it is better to run a loop where you store the partial results in a temp table. Then you can toggle on which column you are making lookups on. But I leave it to others to explore that area.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points

    You appear to be saying you have a parent/child relationship.

    I suggest you start here:

    0 comments No comments

  2. ETL vs ELTL 26 Reputation points

    Please find the attached zipped file for DDL and DML script.
    Table name: ERPX

    you can try for ERPCustomer_BK ='5|75388|US-CA'