How to Update Business Partner Addresses in SAP Business One Using DTW

Managing Business Partner addresses in bulk—whether billing or shipping—can be time-consuming if done manually. Thankfully, the Data Transfer Workbench (DTW) in SAP Business One allows you to efficiently update or append address records with the right setup.

This guide walks you through:

• Avoiding the common "ODBC -2035" error

• Properly assigning LineNum for updates and additions

• Structuring your DTW import files correctly

Common Error: "This Entry Already Exists in the Following Tables (ODBC -2035)" 

This error typically appears when:

• You try to insert duplicate address entries

• You incorrectly use the BusinessPartners template instead of BPAddresses

• You set the wrong LineNum values in your import file

Let’s walk through the correct method step by step.

Use the Correct Template 

Always use the BPAddresses.txt template for importing addresses.
Do not attempt to load address data directly through the BusinessPartners.txt header file—use it only to assign default addresses using the address names from your BPAddresses import.

✅ Example: Use BusinessPartners.txt to assign “Bill2” and “Ship2” as default addresses that were defined in BPAddresses.txt,

Step 1: Get the Correct LineNum Values 

SAP B1 uses LineNum to identify the sequence of addresses per Business Partner in DTW—not the physical LineNum in the CRD1 table.

Use the query below to get the current address order and automatic LineNum Determination. 

✅ For Microsoft SQL Server

Version lower than 9.0 PL09

SELECT 
    CardCode, 
    Address, 
    LineNum AS CRD1_LineNum,
    ROW_NUMBER() OVER (PARTITION BY CardCode ORDER BY LineNum) - 1 AS DTW_LineNum,
    AdresType, 
    Street 
FROM 
    CRD1 
WHERE 
    CardCode = 'YourCardCode' 
ORDER BY 
    LineNum

Version 9.0 PL09 to 9.0 PL16 and 9.1 PL06 or lower

SELECT 
    CardCode, 
    Address, 
    LineNum AS CRD1_LineNum,
    ROW_NUMBER() OVER (PARTITION BY CardCode ORDER BY Address, AdresType, LineNum) - 1 AS DTW_LineNum,
    AdresType, 
    Street 
FROM 
    CRD1 
WHERE 
    CardCode = 'YourCardCode' 
ORDER BY 
    CardCode, Address, AdresType, LineNum

Version 9.1 PL07 and higher or 9.0 PL17 and higher

SELECT 
    CardCode, 
    Address, 
    LineNum AS CRD1_LineNum,
    ROW_NUMBER() OVER (PARTITION BY CardCode ORDER BY LineNum, Address, AdresType) - 1 AS DTW_LineNum,
    AdresType, 
    Street 
FROM 
    CRD1 
WHERE 
    CardCode = 'YourCardCode' 
ORDER BY 
    CardCode, LineNum, Address, AdresType

✅ For SAP HANA (All Versions)

SELECT 
    "CardCode", 
    "Address", 
    "LineNum" AS "CRD1_LineNum",
    ROW_NUMBER() OVER (PARTITION BY "CardCode" ORDER BY "LineNum", "Address", "AdresType") - 1 AS "DTW_LineNum",
    "AdresType", 
    "Street" 
FROM 
    "CRD1" 
WHERE 
    "CardCode" = 'YourCardCode' 
ORDER BY 
    "CardCode", "LineNum", "Address", "AdresType"

✏️ Scenario 1: Update All Existing Addresses

If you're updating the street or other fields for every address tied to a Business Partner:

1. Run the query above to get the correct LineNum for each address.

2. Prepare your BPAddresses.txt file, entering the correct CardCode, Address, and LineNum values along with the new data (e.g., updated street names).
   

3. In DTW, choose Update Existing Data during Step 2 of the wizard.

4. Complete the import.

Scenario 2: Update One Specific Address 

If you're only modifying one address:

1. Run the query to get the address sequence and find the matching row for your update.

2. Prepare your file with just the relevant CardCode, Address, and LineNum.
   

3. Import using Update Existing Data in DTW.

Scenario 3: Update When Some Addresses Were Deleted 

If some rows have been deleted, there may be gaps in the CRD1 LineNum, but your DTW still needs continuous sequence numbers:

1. Run the query—your output may skip LineNum values (e.g., 0, 2, 5).

2. In DTW, simply resequence the rows using ROW_NUMBER - 1 logic and update based on that.
   

3. Use DTW to update as usual.

➕ Scenario 4: Add a New Address to an Existing List

To add an address to a Business Partner who already has others:

1. Run the query and find out how many rows exist.

2. In your import file:

   • Include placeholder rows for existing addresses with CardCode and correct LineNum.

   • Add the new address using the next LineNum in sequence (e.g., if 3 rows exist, use LineNum = 3 for the new address).
     

3. In Step 3 of DTW, select Add New Data and Update Existing Data.

4. Proceed with import.

Key Reminders 

• LineNum must always start from 0 and increment by 1 without gaps in your DTW file—even if CRD1 has missing values.

• Always run the correct query to get the current address sequence.

• Use the BPAddresses template—not BusinessPartners—for inserting or updating address records.

✅ Conclusion

Updating or appending Business Partner addresses using DTW in SAP Business One is powerful—but precision is key. By generating correct LineNum values and using the appropriate import options, you can avoid errors like ODBC -2035 and keep your Business Partner master data clean and reliable.

Need help troubleshooting your import or automating this for multiple partners? Reach out—we’re here to help!