SQL Server - How to insert multiple items using loops in SQL

Question

My current SQL query is

INSERT INTO JobLocations(JobLocationId, JobId, DeviceId)
    SELECT 
        ((SELECT MAX(JobLocationId) FROM JobLocations) + 
                 ROW_NUMBER() OVER (ORDER BY jh.GroupId)),
        jh.GroupId, 
        MAX(jh.SerialNumber)
    FROM 
        JobsHistory jh 
    INNER JOIN 
        dbo.Devices d ON jh.SerialNumber = d.DeviceId
    GROUP BY 
        jh.GroupId 
    ORDER BY 
        jh.GroupId ASC

But the issue with this code is that it is entering ONLY the maximum value for jh.SerialNumber whereas I want to enter ALL the values in jh.SerialNumber. However, when I remove the MAX() surrounding jh.SerialNumber, I receive the following error:

Column 'JobsHistory.SerialNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So is there a simple way for me to inset all the SerialNumbers? I understand the error is happening because it does not know "which" SerialNumber to select and I've looked up this question in past posts like this but it doesn't explain how to just insert ALL the entries and rather inserts only the MAX value.

EDIT: example:

JobsHistory:

GroupId | SerialNumber
--------+--------------
2732        335890264
2732        335890261
2732        335890437
2732        335890438

It would only insert 335890438 instead of all 4

Some sample data and expected result would be helpful. It's difficult to just go off a query that doesn't do what you want. — Error_2646, Jul 30 '21 at 17:15
Why do you join to Device but use no columns from that table? The SerialNumber column in your history table is, in fact, the same as DeviceId so the join serves no useful purpose. — SMor, Jul 30 '21 at 18:07
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — SMor, Jul 30 '21 at 18:07

score 1 · Accepted Answer · answered Jul 30 '21 at 18:39

Based on your description, it seems you simply need to add SerialNumber to the GROUP BY.

INSERT INTO JobLocations(JobLocationId, JobId, DeviceId)

SELECT
  (
    (
      SELECT MAX(JobLocationId)
      FROM JobLocations
    ) + ROW_NUMBER() OVER (ORDER BY jh.GroupId)
  )
, jh.GroupId
, jh.SerialNumber

FROM JobsHistory jh INNER JOIN dbo.Devices d ON jh.SerialNumber = d.DeviceId

GROUP BY jh.GroupId
, jh.SerialNumber

ORDER BY jh.GroupId ASC

But that assumes your question is complete. Is there a requirement for the relationship between the first (unnamed) column that your query outputs and GroupId? If so, I can revise my answer.

SQL Server - How to insert multiple items using loops in SQL

1 Answers1