Creating Dupe records in SQL Server-CodePudding

Background: We are in process of migrating our ETL from an old database to a new database which record data differently. In order to be as close to our old system (which creates 1 record for sale and another if the sale was cancelled and the NEW system creating only the cancelled record), I need to create another record for these sales so that I match 1:1 between the old and new database.

What would be an effective way to achieve this. For example, I can get 10k records that need duplication. These are based on specific conditions in the database.

Here is the sample of data :

AcctNum           CancelFlag             SaleDate          SalePrice         
-----------------------------------------------------------------------
  1                    Y                 2022-06-27        100.00
  2                    Y                 2022-05-10        156.00

Desired output:

AcctNum            CancelFlag             SaleDate          SalePrice
---------------------------------------------------------------------
  1                    Y                  2022-06-27         100.00
  1                                       2022-06-27        -100.00
  2                    Y                  2022-05-10         156.00
  2                                       2022-05-10        -156.00

Please note that on the duped row, I won't have the CancelFlag = Y and the SalePrice will be flipped to a negative sign to indicate the cancellation of the sale.

Thanks for taking the time to read.

CodePudding user response：

So if I understand correctly, you basically want to create new rows for each of those rows selected , with the CheckFlag off, and the amount negated - right?

So try something like this:

INSERT INTO dbo.YourTable (AcctNum, CancelFlag, SaleDate, SalePrice)
    SELECT
        AcctNum, NULL, SaleDate, SalePrice * -1.0
    FROM
        dbo.YourTable
    WHERE
        CancelFlag = 'Y'
        AND SalePrice > 0.0;

Does that do what you're looking for? You might need to tweak your WHERE clause to get precisely those existing rows from the table that you're really interested in.