**Problem:** My team is currently undergoing an ERP migration from an ECC system to a new S/4 Hana System. As part of go-live, our team needs to replicate all of the tables out of the S/4 system and into our SLT schema which will host the data. A majority of the tables will be handled by SLT replication out of SAP. But, due to tight timelines, we have identified 4 tables that would require multiple days of replication. The idea is to copy out the existing data from the remote source (ABAP/SDA) and place in our SLT schema. Once that is accomplished, we can activate the point forward replication and allow all new or modified records to be updated view SLT replication.

**Approaches Attempted:** Our current approach is to establish an SDA connection with the backend S/4 database and break the data down by year to insert into our local table using a stored procedure. There have been a number of issues that have arose with this approach, but it is currently working. It's just super slow.

**Questions for the forum:**

* Is this how you would approach this type of problem? If not, what is your proposed solution?
* Do you see anything in the target table that needs to be customized in order to increase performance?
* Does anything stand out to you in the stored procedure that may need to be tuned?

**Example:**
Let's pretend we have a source table named: **A\_tbl**

* 500 million records in the A\_tbl
* Roughly 500 columns wide

Then we will have our target table: **B\_tbl**

* Same # of columns as A\_tbl (500)
* Round Robin Partition of 12
* Indexed on 5 columns

**Current Procedure:**

```
CREATE OR REPLACE procedure LOAD_B_TBL_FROM_A_TBL ()
as
begin

    declare v_offset_nbr integer;
    declare v_record_count integer;
    declare v_commit_count integer;
    declare i integer;
    declare v_year nvarchar(4);
    declare v_record_per_commit_count CONSTANT INT = 1000000; 
    declare v_table_name CONSTANT NVARCHAR(30) = 'A_TBL';   
    declare v_start_year CONSTANT INT = 2011;
    declare v_end_year CONSTANT INT = 2022;
    declare year_nbr integer;

    for year_nbr in v_start_year..v_end_year do

        select IfNull(max(offset_nbr),0) into v_offset_nbr from B_TBL_SCHEMA.bulk_load_log where table_name = :v_table_name AND year_nbr = to_varchar(year_nbr); -- Get offset number of records

        select count(*) into v_record_count from A_TBL_SCHEMAA_TBL A_TBL WHERE A_TBL.YEAR = to_varchar(year_nbr); -- Count the source records.

        v_record_count = v_record_count - v_offset_nbr; -- Subtract out the records already committed for the current year. Failsafe if procedure fails

        v_commit_count = v_record_count / v_record_per_commit_count; -- Number of times we need to loop

        IF v_record_count < v_record_per_commit_count THEN -- Don't enter the loop if it's not necessary
            INSERT INTO B_TBL_SCHEMA.B_TBL (
                SELECT * FROM A_TBL_SCHEMAA_TBL
                WHERE A_TBL.YEAR = to_varchar(year_nbr)
            ); -- Insert into our target table 
            COMMIT;

            insert into B_TBL_SCHEMA.bulk_load_log values(
                v_table_name,
                to_varchar(year_nbr),
                :v_offset_nbr,
                now()
            ); -- Insert into a logging table to keep up with offset

        ELSE

            for i in 0..v_commit_count do -- Loop number of commit times. (500 million / 1 million) = 500 commits necessary to process entire table

                INSERT INTO B_TBL_SCHEMA.B_TBL (
                    SELECT * FROM A_TBL_SCHEMAA_TBL 
                    WHERE A_TBL.YEAR = to_varchar(year_nbr)
                    LIMIT :v_record_per_commit_count OFFSET :v_offset_nbr
                ); -- Insert into our target table
                COMMIT;

                v_offset_nbr = v_offset_nbr + v_record_per_commit_count; -- Update the offset before logging so we know where to begin if procedure fails

                insert into B_TBL_SCHEMA.bulk_load_log values(
                    v_table_name,
                    to_varchar(year_nbr),
                    :v_offset_nbr,
                    now()
                ); -- Insert into logging table to keep up with offset
                COMMIT;
            end for;
        end if;
    end for;
end;

```