CODEWITHSUNDEEP
sql-server
Josimar
Josimar

Reputation: 85

Create a SELECT with defined number of rows

I have a stored procedure that should insert some random rows in a table depending on the amount values

@amount1 INT --EligibilityID = 1
@amount2 INT --EligibilityID = 2
@amount3 INT --EligibilityID = 3

Maybe the obvious way is to use TOP(@amount) but there are a lot of amount values and the second select is much larger. So, I was looking for a way to do it in a single statement if possible.

INSERT INTO [dbo].[CaseInfo]
  SELECT ([EligibilityID],[CaseNumber],[CaseMonth])
  FROM (
    SELECT TOP(@amount1) [EligibilityID],[CaseNumber],[CaseMonth]
    FROM [dbo].[tempCases]
    WHERE [EligibilityID] = 1
  )

INSERT INTO [dbo].[CaseInfo]
  SELECT ([EligibilityID],[CaseNumber],[CaseMonth])
  FROM (
    SELECT TOP(@amount2) [EligibilityID],[CaseNumber],[CaseMonth]
    FROM [dbo].[tempCases]
    WHERE [EligibilityID] = 2
  )

INSERT INTO [dbo].[CaseInfo]
  SELECT ([EligibilityID],[CaseNumber],[CaseMonth])
  FROM (
    SELECT TOP(@amount3) [EligibilityID],[CaseNumber],[CaseMonth]
    FROM [dbo].[tempCases]
    WHERE [EligibilityID] = 3
  )

Upvotes: 0

Views: 97

Answers (1)

George Menoutis
George Menoutis

Reputation: 7240

I would recommend to use row_number, partitioned by eligibilityID, and then compare it with a case statement to select the correct variable each time:

INSERT INTO [dbo].[CaseInfo]
  SELECT ([EligibilityID],[CaseNumber],[CaseMonth])
  FROM (
    SELECT [EligibilityID],[CaseNumber],[CaseMonth]
           ,row_number() over (partition by EligibilityID order by CaseNumber) as rn -- you haven't mentioned an ORDER BY, you can change it here
    FROM [dbo].[tempCases]
  ) as table1
  where rn<=case
                when EligibilityID=1 then @amount1
                when EligibilityID=2 then @amount2
                when EligibilityID=3 then @amount3
            end

Upvotes: 1

Related Questions