Is it possible INSERT SELECT a collection of aggregate values, then Update another table based on the @@IDENTITY values of the inserts you just made?

Question

I'll begin by admitting that my problem is most likely the result of bad design since I can't find anything about this elsewhere. That said, let's get dirty.

I have an Activities table and an ActivitySegments table. The activities table looks something like:

activityid (ident) | actdate (datetime) | actduration (datetime) | ticketnumber (numeric) |

ActivitySegments looks something like

segmentid (ident) | ticketid (numeric) | activityid (numeric) | startdate | starttime | enddate | endtime

This is a time tracking function of an intranet. The "old way" of doing things is just using the activity table. They want to be able to track individual segments of work throughout the day with a start/stop mechanism and have them roll up into records in the activities table. The use case is the user should be able to select any/all segments that they've worked on that day and have them be grouped by ticketid and inserted into the activity table. I have that working. I'm sending a string of comma separated values that correspond to segmentids to a sproc that puts them in a temp table. So I have the above two tables and a temp table with one column of relevant segmentids. Can't they all just get along?

What I need is to take these passed activitysegment Ids, group them by ticket number and sum the duration worked on each ticket (I already have the sql for that). Then insert this dataset into the activities table BUT also get the new activityid @@identity and update the activitiessegments table with the appropriate value.

In procedural programming I'd for loop the insert, get the @@identity and do something else to figure out which segmentids went into creating that activityid. I'm pretty sure I'm thinking about this all wrong, but the deadline approaches and I've been staring at SQL management studio for two days, wasted sheets of paper and burned through way too many cigarettes. I see SQL for Smarties in my near future, until then, can someone help me?

Answer 1

It can be tricky to implement the case of (1) do an insert of ranges and (2) use the identity values generated by them.

One approach is to have some kind of tracking column on the table that generates the Id. So for example add a TransactionGUID (uniqueidentifier) or something to the table that generates the identity you want to capture. When you do insert the rowset to this table you specify a given GUID and can then harvest the set of identity values after the insert completes.

The other common approach is just to to it iteratively like you mentioned. Probably there is a better way to architect what you want to do, but if you must use your current approach (and if I understand correctly what it is you are doing) then adding the TransactionGUID may be the easiest fix.

Answer 2

try this approach:

declare @x table (tableID int not null primary key identity (1,1), datavalue varchar(10) null)
INSERT INTO @x values ('one')
INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('cccc')

declare @y table (tableID int not null primary key               , datavalue varchar(10) null)

declare @count int ---------------FROM HERE, see comment
set @count=5;
WITH hier(cnt) AS
        (
        SELECT  1 AS cnt
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE    cnt < @count
        ) -----------------------To HERE, see comment
INSERT INTO @x
    (datavalue)
    OUTPUT INSERTED.tableID, INSERTED.datavalue
    INTO @y
SELECT
    'value='+CONVERT(varchar(5),h.cnt)
    FROM hier  h
    ORDER BY cnt DESC


select '@x',* from @x  --table you just inserted into
select '@y',* from @y  --captured data, including identity

here is output of the SELECTs

     tableID     datavalue
---- ----------- ----------
@x   1           one
@x   2           aaaa
@x   3           cccc
@x   4           value=5
@x   5           value=4
@x   6           value=3
@x   7           value=2
@x   8           value=1

(8 row(s) affected)

     tableID     datavalue
---- ----------- ----------
@y   4           value=5
@y   5           value=4
@y   6           value=3
@y   7           value=2
@y   8           value=1

The "FROM HERE" - "TO HERE" is just a fancy way to create a table to join to, you can use your own table to join to there...

use @y to process your updates, update from and join it in...