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

Question

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..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

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: 110208 More actions · Answer 2

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 · Answer 3

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);