Maintaining a running variable during a batch insert

Question

I have a stored procedure that does a batch insert. One of the columns is being populated with a value that needs to be calculated using the previously inserted row's value, or a default if this is the first insert.

How can I accomplish this in SQL Server?

An example of what I'm trying to accomplish:

INSERT INTO [my_table] ([other_id], [some_date])
SELECT [id], fn_get_next_valid_time(<previously_inserted_date>)
FROM [other_table]

Feel free to edit the title, I can't think of a more appropriate one. — Collin Dauphinee, Feb 14 '15 at 01:59
this post on stack overflow should answer your question http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance — Wombelite, Feb 14 '15 at 05:00

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Running totals have been asked about a lot, I'll assume you're not just trying to sum the numbers. However, you can still probably use a self join and group by to get what you need, the final answer here just happened to be the first I saw that illustrated the technique. You do need to devise a formula that can express the previous number with this approach.

If the multiply by 8 is what you're actually trying to do, you could use ROW_NUMBER and POWER, assuming the default is 1:

INSERT INTO [my_table]([other_id], [number])
SELECT [id], POWER(8, ROW_NUMBER() OVER (ORDER BY [id]) - 1) AS [Num]
FROM [other_table]

If you had SQL Server 2012+, you could use the LAG function:

INSERT INTO [my_table]([other_id], [number])
SELECT [id], LAG(Num, 1, 1) OVER (ORDER BY [id])
FROM [other_table]

EDIT:

Based on the comments, I think this should do the trick, as long as there aren't more than 12 things to schedule in an hour. And I'm sure there's a better way to convert that hour to a time. I don't have my server handy to test.

INSERT INTO [my_table]([other_id], [other_time])
SELECT [id], 
    DATEADD(mi, 
       5 * (ROW_NUMBER() OVER (PARTITTION BY [hour_to_schedule] ORDER BY [id]) - 1),
       CAST(CAST([hour_to_schedule] AS VARCHAR(2)) + ':00:00' AS TIME))
FROM [other_table]

You're right; in the actual problem, the column is a datetime. It can't be calculated. — Collin Dauphinee, Feb 14 '15 at 03:01
Are you taking a base date and then adding time to it? And is it safe to order by `[id]`? — Matthew Jaspers, Feb 14 '15 at 03:20
No, there is logic involved. I have a table containing an `hour_to_schedule` column, and the inserts are ordered by this column. For each row with the same `hour_to_schedule`, the resulting datetime is incremented by 5 minutes. 13, 13, 13, 10 would schedule at 13:00, 13:05, 13:10, and 10:00 — Collin Dauphinee, Feb 14 '15 at 03:44

Maintaining a running variable during a batch insert

1 Answers1