是否可以INSERT SELECT聚合值的集合，然后根据您刚刚创建的插入的@@ IDENTITY值更新另一个表？

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:

我有一个Activities表和一个ActivitySegments表。活动表看起来像:

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

activityid(ident)| actdate(datetime)| actduration(datetime)| ticketnumber(数字)|

ActivitySegments looks something like

ActivitySegments看起来像

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

segmentid(ident)| ticketid(数字)| activityid(数字)| startdate | starttime |结束|时间结束

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?

这是内联网的时间跟踪功能。做事的“老方法”就是使用活动表。他们希望能够使用启动/停止机制全天跟踪各个工作段,并将它们汇总到活动表中的记录中。用例是用户应该能够选择他们当天工作的任何/所有段,并将它们按ticketid分组并插入到活动表中。我有那个工作。我发送了一串逗号分隔值,这些值对应于segmentids到sproc,它们将它们放在临时表中。所以我有上面的两个表和一个临时表,其中包含一列相关的segmentids。难道他们都不能相处吗?

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.

我需要的是获取这些通过的活动段ID,按票号对它们进行分组,并对每张票上的工作持续时间求和(我已经有了sql)。然后将此数据集插入到活动表中,同时获取新的activityid @@ identity并使用适当的值更新activitiessegments表。

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?

在程序编程中,我将循环插入,获取@@身份并执行其他操作以确定哪些segmentid用于创建该activityid。我很确定我认为这一切都错了,但截止日期临近,我一直盯着SQL管理工作室两天,浪费了纸张,并烧掉了太多的香烟。我在不久的将来会看到针对Smarties的SQL,在那之前,有人可以帮助我吗?

2 个解决方案

#1

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

这是SELECT的输出

     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...

“FROM HERE” - “TO HERE”只是一种创建表格加入的奇特方式,您可以使用自己的表格加入到那里......

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

使用@y处理您的更新,更新并加入...

#2

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

实现(1)插入范围和(2)使用它们生成的标识值的情况可能是棘手的。

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.

一种方法是在表上生成某种生成Id的跟踪列。因此,例如,将TransactionGUID(uniqueidentifier)或其他内容添加到生成您要捕获的标识的表中。当您将行集插入此表时,您指定一个给定的GUID,然后可以在插入完成后收集一组标识值。

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.

另一种常见的方法就是像你提到的那样迭代地进行迭代。可能有更好的方法来构建您想要做的事情,但如果您必须使用当前的方法(如果我正确理解您正在做什么),那么添加TransactionGUID可能是最简单的修复方法。

#1