CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Justin Kright
Justin Kright

Reputation: 65

Iterate through a view inserting to a table

I have a table of customer orders. I need to insert products to a Purchase Order if they exist on the correlating Invoice. Quite a few things rely on data from the Purchase Order table so all I can do is build views to assist with the insert.

View Maxes, grabs the MAX(_) + 1 of fields that must increment with each insert. 

NextSalesOrder   FIFOID    ChangeID    NextPO   
---------------------------------------------
102515           300005    26665       1234

I built another view of products that need to be inserted to a purchase order. 

Invoice       Product    ChangeID
---------------------------------
102515        105        26665   
102516        144        26665
102517        105        26665

I need to insert all 3 of those values to the Purchase Order table, but the ChangeID must increment for each. I cannot change the table schema. 

INSERT INTO [Purchase Order] (Invoice, Product, ChangeID)
SELECT TOP(1) T1.Invoice, T1.Product, T2.ChangeID
FROM dbo.[Invoice] T1, dbo.[Maxes] T2

The above code works fine as long as i'm using TOP(1), but I need a way to iterate through all records of the [Invoice] view while incrementing the ChangeID

I see 2 ways of achieveing what I need,

  1. Insert TOP(1) from the view to the Purchase Order table and iterate through the view until all rows have been inserted.

  2. Alter the product view so the ChangeID increments on its own.

I cannot dump the view into a temporary table as the ChangeID does not increment, so an iteration through Top(1) is necessary (unless someone knows how to do that).

I'm stuck here and am currently just calling the procedure multiple times so all rows are inserted, any advice would be great.

Thanks.

Upvotes: 1

Views: 69

Answers (1)

sqluser
sqluser

Reputation: 5672

You can use Ranking functions to rank your rows and use it in your query. If you already have ChangeId in your Invoices view, there is no need to make another join with Maxes view

INSERT INTO [Purchase Order] (Invoice, Product, ChangeID)
SELECT Invoice
      ,Product
      ,ROW_NUMBER() OVER(PARTITION BY ChangeID ORDER BY ChangeID) + ChangeId - 1
FROM dbo.[Invoice]

This part ROW_NUMBER() OVER(PARTITION BY ChangeID ORDER BY ChangeID) + ChangeId - 1 gives a row number for each ChangeId then add it to ChangeId and minus 1 from the result

Sample usage

DECLARE @tbl TABLE (Invoice INT,       Product INT,    ChangeID INT)

INSERT @tbl
SELECT 102515,        105  ,      26665 UNION ALL 
SELECT 102516 ,       144 ,       26665 UNION ALL 
SELECT 102517  ,      105,        26665

SELECT * FROM @tbl

SELECT ROW_NUMBER() OVER(PARTITION BY ChangeID ORDER BY ChangeID) + ChangeId - 1 NewChangeId
       ,ChangeId
FROM @tbl

Output

NewChangeId ChangeId
26665       26665
26666       26665
26667       26665

Upvotes: 1

Related Questions