Is it possible for an INSERT SELECT to collect aggregate values and then update another table based on the @@ IDENTITY values of the inserts you are inserting?
I'll start by admitting that my problem is most likely the result of poor design, as I can't find anything else about it. However, it's dirty.
I have an Activities table and an ActivitySegments table. The action table looks something like this:
activityid (ident) | actdate (datetime) | actduration (datetime) | ticket number (numeric) |
ActivitySegments looks something like this:
segmentid (ident) | ticket (numeric) | activityid (numeric) | start | start time | enddate | end time
This is an intranet time tracking feature. The "old way" of doing things is just using an action table. They want to be able to track individual work segments throughout the day using a start / stop mechanism and summarize them to a record in an activity table. Use case - the user should be able to select any / all segments they worked on that day and group them by tickets and insert them into the action table. It works for me. I am sending a comma separated string of values corresponding to the segments to sproc, which puts them in the temp table. Thus, I have the two tables above and a temporary table with one column of the respective shards. Can't they just get along?
I need to have these missing activity IDs, group them by ticket number and sum the duration of each ticket (I already have sql for this). Then insert this dataset into the activity table, but also get a new activityid @@ and update the activitiessegments table with the appropriate value.
In procedural programming, I would loop the insert, get the @@ id, and do something else to figure out which segmenttides went into creating this ActivityID. I'm pretty sure I'm thinking this all wrong, but the deadline is coming up and I've been staring at SQL management studio for two days, wasting sheets of paper and burning too many cigarettes. I am seeing SQL for Smarties in the near future, can anyone help me?
a source to share
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 the SELECT is output
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
"FROM HERE" - "HERE" is just a fancy way to create a table to join, you can use your own table to join ...
use @y to handle your updates, update and attach to it in ...
a source to share
It can be tricky to implement the case of (1) inserting ranges and (2) using the credentials they generate.
One approach is to have some sort of tracking column in the table that generates an id. So for example add a TransactionGUID (uniqueidentifier) or something to the table that generates the ID you want to capture. When you insert a rowset into this table, you supply the given GUID and then you can collect the set of credentials after the insert is complete.
Another common approach is to do just that, iteratively as you mentioned. There is probably a better way to architect what you want to do, but if you must use your current approach (and if I understand correctly what you are doing) then adding a TransactionGUID might be the simplest fix.
a source to share