How to run a view every 1000 rows to add in table?

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  November 27, 2015 at 2:52 am

  #313386

  I have a view that have a total of rows 30Million. When i start mij SSIS package and my view runs the view make mij TEMP DB full.. and stop running in SSIS.

  How can I run mij view that he know that he need to add the 1000 and then going on with the next 1000 rows:

  I have the following view:

  SELECT

  CAL.D_CalendarSKey,

  CAL.[Date] AS StockDate,

  SUB.D_LocationSkey,

  SUB.D_ProductSkey,

  SUB.DataAreaID,

  SUB.SharedDataAreaID,

  SUB.LocationCode,

  SUB.ProductCode,

  SUB.SourceCode,

  SUB.[StockTransaction (#)],

  SUB.[Gross Stock on Hand (#)],

  SUB.[Gross Stock on Hand (curr)],

  SUB.[Goods in Transit (#)],

  SUB.[Goods in Transit (curr)],

  SUB.[Reserve (#)],

  SUB.[Reserve (Curr)],

  SUB.[Stock Assigned (#)],

  SUB.[Stock Assigned (Curr)],

  SUB.[Gross Stock (#)],

  SUB.[Gross Stock (Curr)],

  SUB.[Net stock],

  SUB.[Net Stock (Curr)],

  SUB.[Net Stock on Hand (#)],

  SUB.[Net Stock on Hand (Curr)]

  FROM

  DFL.D_Calendar CAL

  INNER JOIN [DFL].[TEMP_STOCK_LEVELS_1] SUB

  ON CAL.D_CalendarSKey>=SUB.FromStockSkey

  AND CAL.D_CalendarSKey<=SUB.ToStockSKey

  WHERE CAL.Date<=GETDATE()

  AND year(CAL.DATE)=2015

  In the picture you my statement to select and join with the product dimension..

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  November 27, 2015 at 8:00 am

  #1842638

  There's just not enough information to even begin to make a suggestion here. For example, we don't know what D_CalendarSKey is nor why there'd be a stock related subkey in a calendar table to begin with. The join in the "view" looks like it spawns a massive many-to-many join.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 30, 2015 at 4:41 pm

  #1843060

  Jeff,

  There is enough information for one suggestion:

  WHERE CAL.Date<=GETDATE()

  AND CAL.DATE>='20150101'

  Even for two:

  Clustered index on CAL.Date

  

  _____________
  Code for TallyGenerator

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  December 2, 2015 at 1:01 am

  #1843413

  What is the syntax to make this happen on that column?

  https://msdn.microsoft.com/en-us/library/ms186342.aspx

  is that this syntax?

  -- Create a clustered index called IX_TestTable_TestCol1

  -- on the dbo.TestTable table using the TestCol1 column.

  CREATE CLUSTERED INDEX IX_TestTable_TestCol1

  ON dbo.TestTable (TestCol1);