Insert Into and Identity key

Transact-SQL
1

Here is my situation.

I have got a Details table

   CREATE TABLE [dbo].[tblDetalles](
[Pd_Id] [int] NOT NULL,
[Pd_Pr_Id] [int] NOT NULL,
[Pd_Ps_Id] [int] NOT NULL,
[Pd_Value] [int] NOT NULL,
[S_Id] [int] NULL,
   CONSTRAINT [PK_tblPrestacionDetalle] PRIMARY KEY CLUSTERED 
  (
[Pd_Id] ASC
 ) ON [PRIMARY] )
GO

Which contains the following data:

INSERT INTO [dbo].[tblDetalles] ([Pd_Id],[Pd_Pr_Id],[Pd_Ps_Id] ,Pd_Value) VALUES (1,1,1 ,1000)
GO

INSERT INTO [dbo].[tblDetalles] ([Pd_Id],[Pd_Pr_Id],[Pd_Ps_Id] ,Pd_Value) VALUES (2,1,2 ,2000)
GO

 INSERT INTO [dbo].[tblDetalles] ([Pd_Id],[Pd_Pr_Id],[Pd_Ps_Id] ,Pd_Value) VALUES (3,1,2 ,4000)
 GO

 INSERT INTO [dbo].[tblDetalles] ([Pd_Id],[Pd_Pr_Id],[Pd_Ps_Id] ,Pd_Value) VALUES (4,1,3 ,4000)
 GO
 INSERT INTO [dbo].[tblDetalles] ([Pd_Id],[Pd_Pr_Id],[Pd_Ps_Id] ,Pd_Value) VALUES (5,1,3 ,5000)
GO

And a "Sum" table:

CREATE TABLE [dbo].[tblSum](
[S_Id] [int] IDENTITY(1,1) NOT NULL,
[S_Pr_Id] [int] NOT NULL,
[S_Ps_Id] [int] NOT NULL,
[S_Value] [int] NOT NULL,
  CONSTRAINT [PK_tblBoleta] PRIMARY KEY CLUSTERED 
  (
[S_Id] ASC
  )
   ) ON [PRIMARY]

 GO

To feed the sum table I am running the following script (IDs of rows to select are passed down by the user):

INSERT INTO [dbo].[tblSum]
       ([S_Pr_Id]
       ,[S_Ps_Id]
       ,[S_Value])
  SELECT        Pd_Pr_Id, Pd_Ps_Id, SUM(Pd_Value) AS Expr1
  FROM            dbo.tblDetalles
  WHERE        (Pd_Id IN (2, 3, 4))
  GROUP BY Pd_Pr_Id, Pd_Ps_Id

At this stage I have got this
Table Details
1 1 1 1000 NULL
2 1 2 2000 NULL
3 1 2 4000 NULL
4 1 3 4000 NULL
5 1 3 5000 NULL

Table Sum
1 1 2 6000
2 1 3 4000

What I need to do is that update the table Details with the inserted key of table Sum to identify in which sum the details has been added.
In another words the Details should look like this
1 1 1 1000 NULL
2 1 2 2000 1
3 1 2 4000 1
4 1 3 4000 2
5 1 3 5000 NULL

Is there a way to make it work in a single Insert Into ... like I did or should I

  1. Work with a cursor in my SP
  2. Work at my application level ?
2

Add MIN(Pd_Id) and MAX(Pd_Id) to the Sum table, then you can use those values to update that range of Pd_Ids in the Detalles table with the correct Sum id.

3

You mean creating a field in the Sum tables where I should concatenate the Pd_Ids ?

4

Not concatenate, just capture the first (min) and last (max) so you can join on an id range when updating all detail rows at once.

5

Not sure if that can be done as Pd_Ids won't necessarily between sequential. Could be 20, 24, 30 for one group by and 21,22,26 for another one.

6

You'd still do the normal join on the key cols as well, the range is just an additional condition to hopefully help reduce I/O on the details table.

UPDATE d
SET ...
FROM #tblSum s /*rows just inserted*/
INNER JOIN dbo.tblDetalles d ON d.Pd_Id BETWEEN s.Pd_Id_Min AND s.Pd_Id_Max AND 
    d.Pd_Pr_Id = s.Pd_Pr_Id AND
    d.Pd_PS_Id = s.Pd_Ps_Id