22/8/23

[chriltola]

i want to insert data into table tbltrxn have column [TXDATE] ,[BUSDATE] ,[DORC] ,[TXNUM] ,[TLTXCD] ,[ACCTNO] ,[CUSTID] ,[CCYCD] ,[TX_Currency] ,[AMT] ,[ACC_BRID] ,[ACTYPE] ,[WSNAME] ,[TXDESC] ,[CIFBRID] ,[ATM] ,[PCT_ID] ,[PCT] ,[PPT] ,[PCTTYPE] . the file i insert is csv that use ','

[chriltola]

$serverName = "your_server_name"; $connectionOptions = array( "Database" => "your_database_name", "Uid" => "your_username", "PWD" => "your_password" );

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) { die(print_r(sqlsrv_errors(), true)); }

$csvFile = "data.csv"; // Path to your CSV file $csvTableName = "temp_csv"; // Temporary table to hold CSV data

// Create a temporary table to hold the CSV data $createTempTableQuery = "CREATE TABLE $csvTableName ( TXDATE datetime, BUSDATE datetime, DORC char(1), TXNUM char(6), TLTXCD char(4), ACCTNO char(18), CUSTID char(10), CCYCD char(3), TX_Currency char(10), AMT double, ACC_BRID char(3), ACTYPE char(4), WSNAME char(10), TXDESC char(100), CIFBRID char(3), ATM char(10), PCT_ID int, PCT varchar(50), PPT varchar(100), PCTTYPE varchar(15) )";

$createTempTableResult = sqlsrv_query($conn, $createTempTableQuery);

if ($createTempTableResult === false) { die(print_r(sqlsrv_errors(), true)); }

// Import data from CSV file into temporary table $importCsvQuery = "BULK INSERT $csvTableName FROM '$csvFile' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)";

$importCsvResult = sqlsrv_query($conn, $importCsvQuery);

if ($importCsvResult === false) { die(print_r(sqlsrv_errors(), true)); }

// Insert data from temporary table into tbltrxn $insertIntoTblTrxnQuery = "INSERT INTO tbltrxn (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, TX_Currency, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, ATM, PCT_ID, PCT, PPT, PCTTYPE) SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, TX_Currency, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, ATM, PCT_ID, PCT, PPT, PCTTYPE FROM $csvTableName";

$insertIntoTblTrxnResult = sqlsrv_query($conn, $insertIntoTblTrxnQuery);

if ($insertIntoTblTrxnResult === false) { die(print_r(sqlsrv_errors(), true)); }

// Clean up by dropping the temporary table $dropTempTableQuery = "DROP TABLE $csvTableName"; $dropTempTableResult = sqlsrv_query($conn, $dropTempTableQuery);

if ($dropTempTableResult === false) { die(print_r(sqlsrv_errors(), true)); }

sqlsrv_close($conn);

[chriltola]

$serverName = "your_server_name"; $connectionOptions = array( "Database" => "your_database_name", "Uid" => "your_username", "PWD" => "your_password" );

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) { die(print_r(sqlsrv_errors(), true)); }

$csvFile = "data.csv"; // Path to your CSV file

// Import data from CSV file directly into tbltrxn $importCsvQuery = "BULK INSERT tbltrxn FROM '$csvFile' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)";

$importCsvResult = sqlsrv_query($conn, $importCsvQuery);

if ($importCsvResult === false) { die(print_r(sqlsrv_errors(), true)); }

sqlsrv_close($conn);

[chriltola]

it not work. pls check sample 1 first row of csv i want to insert.

"2023-01-25 ","2023-01-25 ","C"," 000050062 ","6621","0100210020907 ","0100099445","USD","02","381103.51 ","010","1113","CCD69757 ","ITT REF 2023012400245997 FM US AMT USD381,485.00 LESS COM$381.49 B/O WILDLIFE CONSERVATION SOCIETY ","010","381103.51 ","093117"," "," ","Third-Party "

[chriltola]

$serverName = "your_server_name"; $connectionOptions = array( "Database" => "your_database_name", "Uid" => "your_username", "PWD" => "your_password" );

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) { die(print_r(sqlsrv_errors(), true)); }

$csvFile = "data.csv"; // Path to your CSV file

// Import data from CSV file directly into tbltrxn $importCsvQuery = "BULK INSERT tbltrxn FROM '$csvFile' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)";

$importCsvResult = sqlsrv_query($conn, $importCsvQuery);

if ($importCsvResult === false) { die(print_r(sqlsrv_errors(), true)); }

sqlsrv_close($conn);

[chriltola]

as the sample data i provide the for each column data it have " at the first and ", at the last. pls redo

[chriltola]

$serverName = "your_server_name"; $connectionOptions = array( "Database" => "your_database_name", "Uid" => "your_username", "PWD" => "your_password" );

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) { die(print_r(sqlsrv_errors(), true)); }

$csvFile = "data.csv"; // Path to your CSV file

// Import data from CSV file directly into tbltrxn $importCsvQuery = "BULK INSERT tbltrxn FROM '$csvFile' WITH (FIELDTERMINATOR = ',\"', ROWTERMINATOR = '\"', FIRSTROW = 2)";

$importCsvResult = sqlsrv_query($conn, $importCsvQuery);

if ($importCsvResult === false) { die(print_r(sqlsrv_errors(), true)); }

sqlsrv_close($conn);

[chriltola]

it not work. pls check sample 1 first row of csv i want to insert.

"2023-01-25 ","2023-01-25 ","C"," 000050062 ","6621","0100210020907 ","0100099445","USD","02","381103.51 ","010","1113","CCD69757 ","ITT REF 2023012400245997 FM US AMT USD381,485.00 LESS COM$381.49 B/O WILDLIFE CONSERVATION SOCIETY ","010","381103.51 ","093117"," "," ","Third-Party "

in one row we have 20 column. note each column have "" and we want to remove it the double quote and we after , we go to the next column.

[chriltola]

$serverName = "your_server_name"; $connectionOptions = array( "Database" => "your_database_name", "Uid" => "your_username", "PWD" => "your_password" );

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) { die(print_r(sqlsrv_errors(), true)); }

$csvFile = "data.csv"; // Path to your CSV file

// Read and preprocess the CSV file $preprocessedData = array(); if (($handle = fopen($csvFile, "r")) !== false) { while (($data = fgetcsv($handle, 0, ',')) !== false) { $preprocessedRow = array_map('trim', $data); $preprocessedData[] = $preprocessedRow; } fclose($handle); }

// Insert preprocessed data into tbltrxn foreach ($preprocessedData as $row) { $insertQuery = "INSERT INTO tbltrxn (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, TX_Currency, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIFBRID, ATM, PCT_ID, PCT, PPT, PCTTYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insertParams = $row; // Use preprocessed row as parameters

$insertResult = sqlsrv_query($conn, $insertQuery, $insertParams);

if ($insertResult === false) {
    die(print_r(sqlsrv_errors(), true));
}

}

sqlsrv_close($conn);

[chriltola]

i have following php code to process insert data into table temp. i want to add action on it to delete data by TXNUM from table tbltrxn which is insert into temp already. foreach ($branchValues as $branchValue) { $branchPrefix = substr($branchValue, 0, 3); $trxQuery = "SELECT * FROM tbltrxn WHERE SUBSTRING(TXNUM,1, 3) = '$branchPrefix'";

    echo $trxQuery;
    $trxResult = sqlsrv_query($conn, $trxQuery);

    if ($trxResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    // Perform actions based on the query result for each branch value
    while ($trxRow = sqlsrv_fetch_array($trxResult, SQLSRV_FETCH_ASSOC)) {
        // Perform desired actions for each matching row
        // For example, insert data into the temp table as per your previous requirements
        $TXDATE = $trxRow['TXDATE'];
        $BUSDATE = $trxRow['BUSDATE'];
        $DORC = $trxRow['DORC'];
        $TXNUM = $trxRow['TXNUM'];
        $TLTXCD = $trxRow['TLTXCD'];
        $ACCTNO = $trxRow['ACCTNO'];
        $CUSTID = $trxRow['CUSTID'];
        $CCYCD = $trxRow['CCYCD'];
        $ShortCD = $trxRow['ShortCD'];
        $AMT = $trxRow['AMT'];
        $ACC_BRID = $trxRow['ACC_BRID'];
        $ACTYPE = $trxRow['ACTYPE'];
        $TXDESC = $trxRow['TXDESC'];
        $WSNAME = $trxRow['WSNAME'];
        $CIF_BRID = $trxRow['CIF_BRID'];
        $AMT_ = $trxRow['AMT_'];
        $PCT_ID = $trxRow['PCT_ID'];
        $PCT = $trxRow['PCT'];
        $PPT = $trxRow['PPT'];
        $PCTTYPE = $trxRow['PCTTYPE'];

        $insertQuery = "INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE) VALUES ('$TXDATE','$BUSDATE','$DORC','$TXNUM','$TLTXCD','$ACCTNO','$CUSTID','$CCYCD','$ShortCD','$AMT','$ACC_BRID','$ACTYPE','$TXDESC','$WSNAME','$CIF_BRID','$AMT_','$PCT_ID','$PCT','$PPT','$PCTTYPE')";

        $insertResult = sqlsrv_query($conn, $insertQuery, $insertParams);

        echo $insertQuery;

        if ($insertResult === false) {
            die(print_r(sqlsrv_errors(), true));
        }
    }
}

[chriltola]

foreach ($branchValues as $branchValue) { $branchPrefix = substr($branchValue, 0, 3); $trxQuery = "SELECT * FROM tbltrxn WHERE SUBSTRING(TXNUM, 1, 3) = '$branchPrefix'";

echo $trxQuery;
$trxResult = sqlsrv_query($conn, $trxQuery);

if ($trxResult === false) {
    die(print_r(sqlsrv_errors(), true));
}

// Perform actions based on the query result for each branch value
while ($trxRow = sqlsrv_fetch_array($trxResult, SQLSRV_FETCH_ASSOC)) {
    // Perform desired actions for each matching row
    // For example, insert data into the temp table as per your previous requirements
    $TXDATE = $trxRow['TXDATE'];
    $BUSDATE = $trxRow['BUSDATE'];
    $DORC = $trxRow['DORC'];
    $TXNUM = $trxRow['TXNUM'];
    $TLTXCD = $trxRow['TLTXCD'];
    // ... (other column values)

    $insertQuery = "INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE) VALUES ('$TXDATE','$BUSDATE','$DORC','$TXNUM','$TLTXCD','$ACCTNO','$CUSTID','$CCYCD','$ShortCD','$AMT','$ACC_BRID','$ACTYPE','$TXDESC','$WSNAME','$CIF_BRID','$AMT_','$PCT_ID','$PCT','$PPT','$PCTTYPE')";

    $insertResult = sqlsrv_query($conn, $insertQuery, $insertParams);

    if ($insertResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    // Delete data from tbltrxn after insertion into temp
    $deleteQuery = "DELETE FROM tbltrxn WHERE TXNUM = '$TXNUM'";
    $deleteResult = sqlsrv_query($conn, $deleteQuery);

    if ($deleteResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }
}

}

[chriltola]

i have following code

while ($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) { $branchValues = explode(',', $row['branch']);

// Loop through each branch value and create and execute queries
foreach ($branchValues as $branchValue) {
    $branchPrefix = substr($branchValue, 0, 3);
    $trxQuery = "SELECT * FROM tbltrxn WHERE SUBSTRING(TXNUM,1, 3) = '$branchPrefix'";

    echo $trxQuery;
    $trxResult = sqlsrv_query($conn, $trxQuery);

    if ($trxResult === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    // Perform actions based on the query result for each branch value
    while ($trxRow = sqlsrv_fetch_array($trxResult, SQLSRV_FETCH_ASSOC)) {
        // Perform desired actions for each matching row
        // For example, insert data into the temp table as per your previous requirements
        $TXDATE = $trxRow['TXDATE'];
        $BUSDATE = $trxRow['BUSDATE'];
        $DORC = $trxRow['DORC'];
        $TXNUM = $trxRow['TXNUM'];
        $TLTXCD = $trxRow['TLTXCD'];
        $ACCTNO = $trxRow['ACCTNO'];
        $CUSTID = $trxRow['CUSTID'];
        $CCYCD = $trxRow['CCYCD'];
        $ShortCD = $trxRow['ShortCD'];
        $AMT = $trxRow['AMT'];
        $ACC_BRID = $trxRow['ACC_BRID'];
        $ACTYPE = $trxRow['ACTYPE'];
        $TXDESC = $trxRow['TXDESC'];
        $WSNAME = $trxRow['WSNAME'];
        $CIF_BRID = $trxRow['CIF_BRID'];
        $AMT_ = $trxRow['AMT_'];
        $PCT_ID = $trxRow['PCT_ID'];
        $PCT = $trxRow['PCT'];
        $PPT = $trxRow['PPT'];
        $PCTTYPE = $trxRow['PCTTYPE'];

        $insertQuery = "INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE) VALUES ('$TXDATE','$BUSDATE','$DORC','$TXNUM','$TLTXCD','$ACCTNO','$CUSTID','$CCYCD','$ShortCD','$AMT','$ACC_BRID','$ACTYPE','$TXDESC','$WSNAME','$CIF_BRID','$AMT_','$PCT_ID','$PCT','$PPT','$PCTTYPE')";

        $insertResult = sqlsrv_query($conn, $insertQuery, $insertParams);

        echo $insertQuery;

        if ($insertResult === false) {
            die(print_r(sqlsrv_errors(), true));
        }

        // Delete data from tbltrxn after insertion into temp
        $deleteQuery = "DELETE FROM tbltrxn WHERE TXNUM = '$TXNUM'";
        $deleteResult = sqlsrv_query($conn, $deleteQuery);

        if ($deleteResult === false) {
            die(print_r(sqlsrv_errors(), true));
        }
    }
}

} i want to make it to be sql code to run every 4 min

[chriltola]

To run this SQL code every 4 minutes, you can schedule it using a SQL Server Agent Job. Here's how you can do it:

1. Open SQL Server Management Studio.
2. Connect to your SQL Server instance.
3. In the Object Explorer, expand the "SQL Server Agent" node.
4. Right-click on the "Jobs" node and select "New Job..."
5. In the "New Job" dialog, provide a name for the job.
6. Navigate to the "Steps" section and click "New..."
7. In the "New Job Step" dialog:
   • Provide a step name.
   • Choose the database where your script will run.
   • Select "Transact-SQL script (T-SQL)" as the type.
   • Enter your script in the "Command" box. The entire code you provided should be placed here.
8. Navigate to the "Schedules" section and click "New..."
9. In the "New Job Schedule" dialog:
   • Provide a name for the schedule.
   • Choose the frequency as "Recurring" and set it to occur every 4 minutes.
   • Save the schedule.
10. Review your settings and click "OK" to create the job.

The SQL Server Agent Job you just created will execute the provided script every 4 minutes. Make sure you review and test the job to ensure it runs as expected and does not negatively impact your database performance.

[chriltola]

I see, you want to convert your PHP code into a SQL script. Since your PHP code involves logic and actions like querying, inserting, and deleting, you'll need to split these actions into separate SQL statements and run them as a script using SQL Server's scripting capabilities. However, it's important to note that SQL Server scripting is more about executing queries rather than containing logic like programming languages. Here's an example of how you might structure your actions:

DECLARE @branchValue VARCHAR(500);
DECLARE @branchPrefix VARCHAR(3);
DECLARE @TXDATE DATETIME;
DECLARE @BUSDATE DATETIME;
-- ... (declare other variables)

DECLARE cur CURSOR FOR
SELECT branch FROM YourTable; -- Replace with your actual table name

OPEN cur;
FETCH NEXT FROM cur INTO @branchValue;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix;

    DELETE FROM tbltrxn
    WHERE TXNUM IN (SELECT TXNUM FROM temp WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix);

    FETCH NEXT FROM cur INTO @branchValue;
END;

CLOSE cur;
DEALLOCATE cur;

Please replace YourTable with the actual name of the table containing the branch values. Keep in mind that SQL Server scripting doesn't have the same degree of logic and flow control as programming languages like PHP, so you'll need to structure your actions carefully within the capabilities of SQL.

Remember to thoroughly test any script in a safe environment before applying it to your production database.

[chriltola]

If you want to edit the script that is executed as part of a SQL Server Agent Job, you'll need to modify the job step associated with that script. Here's how you can do it:

1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. In the Object Explorer, expand the "SQL Server Agent" node.
4. Expand the "Jobs" node to see the list of existing jobs.
5. Find the job that contains the script you want to edit, right-click on it, and select "Properties."
6. In the "Job Properties" window, go to the "Steps" tab.
7. Select the step that contains the script you want to edit, and click the "Edit" button.

In the "Edit Job Step" window, you'll be able to modify the details of the step, including the script. Depending on the type of step, you might see different options:

• For a "Transact-SQL script" step, you'll see the "Command" box where you can modify the SQL script.

• For a "Operating system (CmdExec)" step, you'll see the "Command" box where you can modify the command line script.

• For other step types, you'll see relevant options for the specific type of task.

After making your edits, click the "OK" button to save the changes. Remember that any changes you make to the script will affect the behavior of the job. It's a good practice to test your changes in a safe environment before applying them to a production job.

Once you've saved your changes, the SQL Server Agent Job will execute the updated script the next time it runs according to its schedule.

[chriltola]

Certainly! Here's a step-by-step guide on how to create a SQL Server Agent Job and add your previous SQL script to the job step:

1. Open SQL Server Management Studio (SSMS).

2. Connect to your SQL Server instance.

3. In the Object Explorer, expand the "SQL Server Agent" node.

4. Right-click on the "Jobs" node and select "New Job..."

5. In the "New Job" dialog:

   • Enter a name for the job in the "Name" field.
   • Optionally, provide a description for the job.
   • Select the owner of the job from the "Owner" dropdown list.
   • Navigate to the "Steps" section and click the "New" button.

6. In the "New Job Step" dialog:

   • Enter a name for the step in the "Step name" field.
   • Select the database where your script will run from the "Database" dropdown list.
   • Choose "Transact-SQL script (T-SQL)" as the "Type."
   • In the "Command" box, paste your SQL script that you want to run as part of this job step.
   • Click the "OK" button to save the step.

7. Go back to the "New Job" dialog:

   • Navigate to the "Schedules" section and click the "New" button.

8. In the "New Job Schedule" dialog:

   • Provide a name for the schedule.
   • Choose the frequency as "Recurring" and set it to occur at the desired interval (e.g., every 4 minutes).
   • Configure any additional scheduling options.
   • Click the "OK" button to save the schedule.

9. Go back to the "New Job" dialog:

   • Review your settings on the other tabs as needed (Notifications, Alerts, etc.).
   • Click the "OK" button to create the job.

Your SQL Server Agent Job is now set up and scheduled to run the SQL script at the specified interval. The script you provided in the job step will be executed every time the job runs according to the schedule.

Remember to review and test your job in a controlled environment before applying it to your production database. Also, be mindful of the potential impact of running the job frequently, especially if it involves data manipulation or other significant actions.

[chriltola]

as your previous sql i want to add action by compare amount from tblevent with AMT from tbltrxn

[chriltola]

Certainly! If you want to compare the "AMT" column from the tbltrxn table with the "amount" column from the tblevent table and take actions based on the comparison within your SQL Server Agent Job, you can modify the SQL script in the job step. Here's an example of how you might structure it:

DECLARE @branchValue VARCHAR(500);
DECLARE @branchPrefix VARCHAR(3);
-- ... (declare other variables)

DECLARE cur CURSOR FOR
SELECT branch FROM YourTable; -- Replace with your actual table name

OPEN cur;
FETCH NEXT FROM cur INTO @branchValue;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

    INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
    SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
    FROM tbltrxn
    WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
    AND AMT >= (SELECT amount FROM tblevent WHERE branch = @branchValue);

    DELETE FROM tbltrxn
    WHERE TXNUM IN (SELECT TXNUM FROM temp WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix);

    FETCH NEXT FROM cur INTO @branchValue;
END;

CLOSE cur;
DEALLOCATE cur;

In this example, the script inserts records into the temp table based on the comparison between the "AMT" column from tbltrxn and the "amount" column from tblevent. The WHERE clause in the INSERT INTO statement filters records where AMT is greater than or equal to the corresponding "amount" value in tblevent.

Please replace YourTable with the actual name of the table containing the branch values. Additionally, ensure that the columns and table names match your schema.

Remember to thoroughly test this modified script in a safe environment before applying it to your production database.

[chriltola]

please rebuild again as following :

1. select event which have isActive = 1;

2. inert into temp where WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix AND AMT = (SELECT amount FROM tblevent WHERE isActive = 1);

3. delete every record which already insert into temp

[chriltola]

DECLARE @branchValue VARCHAR(500); DECLARE @branchPrefix VARCHAR(3); -- ... (declare other variables)

DECLARE cur CURSOR FOR SELECT branch FROM YourTable WHERE isActive = 1; -- Replace with your actual table name and condition

OPEN cur; FETCH NEXT FROM cur INTO @branchValue;

WHILE @@FETCH_STATUS = 0 BEGIN SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

-- Insert records into temp for active events
INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
SELECT t1.TXDATE, t1.BUSDATE, t1.DORC, t1.TXNUM, t1.TLTXCD, t1.ACCTNO, t1.CUSTID, t1.CCYCD, t1.ShortCD, t1.AMT, t1.ACC_BRID, t1.ACTYPE, t1.WSNAME, t1.TXDESC, t1.CIF_BRID, t1.AMT_, t1.PCT_ID, t1.PCT, t1.PPT, t1.PCTTYPE
FROM tbltrxn t1
INNER JOIN tblevent t2 ON SUBSTRING(t1.TXNUM, 1, 3) = @branchPrefix AND t1.AMT = t2.amount AND t2.isActive = 1;

-- Delete records from tbltrxn that were inserted into temp
DELETE t1
FROM tbltrxn t1
INNER JOIN tblevent t2 ON SUBSTRING(t1.TXNUM, 1, 3) = @branchPrefix AND t1.AMT = t2.amount AND t2.isActive = 1;

FETCH NEXT FROM cur INTO @branchValue;

END;

CLOSE cur; DEALLOCATE cur;

[chriltola]

SELECT branch FROM tblevent WHERE isActive = 1;

and query trxn by branch and AMT.

[chriltola]

DECLARE @branchValue VARCHAR(500); DECLARE @branchPrefix VARCHAR(3); -- ... (declare other variables)

-- Select active branches DECLARE cur CURSOR FOR SELECT branch FROM tblevent WHERE isActive = 1;

OPEN cur; FETCH NEXT FROM cur INTO @branchValue;

WHILE @@FETCH_STATUS = 0 BEGIN SET @branchPrefix = SUBSTRING(@branchValue, 1, 3);

-- Query tbltrxn based on branch and AMT
INSERT INTO temp (TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE)
SELECT TXDATE, BUSDATE, DORC, TXNUM, TLTXCD, ACCTNO, CUSTID, CCYCD, ShortCD, AMT, ACC_BRID, ACTYPE, WSNAME, TXDESC, CIF_BRID, AMT_, PCT_ID, PCT, PPT, PCTTYPE
FROM tbltrxn
WHERE SUBSTRING(TXNUM, 1, 3) = @branchPrefix
AND AMT = (SELECT amount FROM tblevent WHERE branch = @branchValue);

FETCH NEXT FROM cur INTO @branchValue;

END;

CLOSE cur; DEALLOCATE cur;