CODEWITHSUNDEEP
sql-servert-sqlcursortemp-tables
user3021830
user3021830

Reputation: 2924

How can I insert dynamic number of rows

I have a material table as source table as follows : 

CREATE TABLE dbo.MATERIAL (
    ID int IDENTITY(1,1) NOT NULL,
    CATEGORY_ID int NOT NULL,
    SECTION_ID int NOT NULL,
    STATUS_ID int NOT NULL
)

also I have a Order table as the target table as follows : 

CREATE TABLE dbo.ORDER (
    ID int IDENTITY(1,1) NOT NULL,
    MATERIAL_ID int NOT NULL
)

I am sending ome data to SQL server to a Stored Procedure and have the following Temp Table created 

DECLARE @temptable TABLE (
    CATEGORY_ID int NOT NULL,
    SECTION_ID int NOT NULL,
    STATUS_ID int NOT NULL,
    COUNT int NOT NULL
)

and had it filled with data as follows : 

CATEGORY_ID    SECTION_ID    STATUS_ID    COUNT
-----------    ----------    ---------    -----
     3             8             1          10
     8             2             2          11 
     4             6             1           8

What I want is to match COUNT number of materials from MATERIAL table which matches ith the given CATEGORY_ID, SECTION_ID and STATUS_ID triple of the same row; then insert those records's IDs to the target table, ORDER.

How can I acomplish this task?

Regards.

Upvotes: 1

Views: 355

Answers (2)

xQbert
xQbert

Reputation: 35323

Threw away a bunch of previous work now that I think I understand requirement.

Here's a working SQL FIDDLE:

This generates a data set called GENROWS that contains rows equal to the count of the max count in temptable. It does this by using a recursive Common table expression (CTE) to generate 1 row for each count of the max count in temptable.
It then uses this data set to join to temptable and material to generate the # of times the material needs to be inserted in order.

I'm not a big fan of using reserved words so I adjusted order to morder and I would recommend adjusting column count otherwise you'll be stuck wrapping words in []'s from time to time.

NOTE: This assumes there will not be duplicates (records with same category_ID, Section_Id and Status_ID) in material table. If there are; then this may or may not behave as expected.

And lastly now that I have a better understanding of what you're after I'm not positive you'll see much of a performance gain compared to using cursors. As the rows have to be generated somehow. This still may work a bit faster because we generate the set and insert all at once as opposed to individually. But there's overhead with producing storing and retrieving the data set produced which may offset this gain. Only testing would tell.

WITH 
GenRows (RowNumber, Val) AS (
   -- Anchor member definition
   SELECT 1 AS RowNumber, (Select max(count) val from temptable) val
   UNION ALL
   -- Recursive member definition
   SELECT a.RowNumber + 1  AS RowNumber, a.val
   FROM   GenRows a
   WHERE  a.RowNumber < a.val
)
Insert into morder (Material_ID)  
SELECT A.ID
FROM material A
INNER JOIN temptable B
   on A.Category_ID = B.Category_ID
  and A.Section_Id = B.Section_Id
  and A.Status_Id = B.Status_ID
INNER JOIN GenRows
  on GenRows.RowNumber <= b.[count]

Upvotes: 1

user3021830
user3021830

Reputation: 2924

I think I have found the solution. I should have used CURSOR. The following code does the trick : 

DECLARE @MATERIAL_ID int
DECLARE @CATEGORY_ID int
DECLARE @SECTION_ID int
DECLARE @STATUS_ID int
DECLARE @COUNT int

DECLARE cur CURSOR LOCAL FOR
SELECT
    CATEGORY_ID,
    SECTION_ID,
    STATUS_ID,
    COUNT
FROM
    @temptable 

OPEN cur
FETCH NEXT FROM cur INTO @CATEGORY_ID, @SECTION_ID, @STATUS_ID, @COUNT
WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO
            dbo.ORDER
            (MATERIAL_ID)
        SELECT
            TOP (@COUNT) ID
        FROM
            dbo.MATERIAL AS m
        WHERE
            m.CATEGORY_ID = @CATEGORY_ID
            AND m.SECTION_ID = @SECTION_ID
            AND m.STATUS_ID = @STATUS_ID

        FETCH NEXT FROM cur INTO @CATEGORY_ID, @SECTION_ID, @STATUS_ID, @COUNT
    END
CLOSE cur
DEALLOCATE cur

Upvotes: 1

Related Questions