Bdtjtk

I wrote some SQL statements that work for updating a single customer. I have to update all the customers when this code gets pushed out.

Right now the customer ID is hardcoded and the SQL statements insert one record based on that ID. Prototype works, now I want to do like 10,000 inserts for all of the customers using the same algorithm.

DECLARE @customerID BIGINT = 47636;

DECLARE @limitFourAdjustment MONEY;

DECLARE @appliesToDateTime DATETIME2(7) = SYSUTCDATETIME();



DECLARE @dp_y INT = DATEPART(YEAR, @appliesToDateTime);

DECLARE @dp_m INT = DATEPART(MONTH, @appliesToDateTime);

DECLARE @dp_w INT = DATEPART(WEEK, @appliesToDateTime);

DECLARE @dp_d INT = DATEPART(DAY, @appliesToDateTime);

DECLARE @dp_h INT = DATEPART(HOUR, @appliesToDateTime);

DECLARE @d_h DATETIME2(7) = DATEADD(HOUR, DATEDIFF(HOUR, 0, @appliesToDateTime), 0);



SELECT

    @limitFourAdjustment = -COALESCE(SUM(COALESCE(Amount, 0)), 0)

FROM 

    [dbo].Transactions

WHERE

    CustomerID = @customerID AND

    IsSystemVoid = 0 AND

    TransactionTypeID IN (SELECT ID FROM TransactionTypes WHERE TransactionTypeGroupID = 3)



INSERT INTO dbo.CustomerAccounts_TransactionSummation (CustomerID, LimitTypeID, Y, M, W, D, H, YMDH, Amount)

VALUES (@customerID, 4, @dp_y, @dp_m, @dp_w, @dp_d, @dp_h, @d_h, @limitFourAdjustment);

I tried adding a while loop, seems like not the fastest solution. Maybe collect the ID's first and then feed it to through the loop? My first attempt below doesn't work because I just get the last customer ID, not a unique one every time.

SELECT @numberOfCustomers = COUNT(*) 

FROM  dbo.Customers



WHILE(@numberOfCustomers > 0)

BEGIN

    SELECT @customerID = ID FROM dbo.Customers



    OTHER LOGIC FROM ABOVE



    SET @numberOfCustomers = @numberOfCustomers - 1;

END

So the question is, how to run these SQL statements (first code block) on every customer's ID?

The key to working with databases is getting your mind around set based operations as opposed to procedural operations. Databases are designed to operate naturally on sets of data at a time, but you have to change how you think about the problem to one where you are manipulating the entire set of data as opposed to one record at a time.

So here is the SQL which I think carry out your complete update in one hit:

INSERT INTO dbo.CustomerAccounts_TransactionSummation (CustomerID, LimitTypeID, Y, M, W, D, H, YMDH, Amount)

  SELECT

    id

    , 4 

    , @dp_y

    , @dp_m

    , @dp_w

    , @dp_d

    , @dp_h

    , @d_h

    , -COALESCE(SUM(COALESCE(Amount, 0)), 0) limitFourAdjustment

  FROM [dbo].Transactions

  WHERE IsSystemVoid = 0

  and TransactionTypeID IN (SELECT ID FROM TransactionTypes WHERE TransactionTypeGroupID = 3)

  --and CustomerID = @customerID

Note that the insert can be combined directly with a select as opposed to using values.