I have a view that will generate approx. 100 million records. I am
using a DTS package to append to a table from this view.

I have disabled Transactions at the package level, and set the batch
size to 1000 and the fetch size to 1000.

The database recovery mode is Simple.

Even with all of these settings, the tempdb still grows before it even
shows a single record being inserted.

I assume BCP or Bulk Insert Task will not work because they only
source from text files.

I do not want any rollback or recoverability for this process, because
I just truncate the table and start over again (this is a data
warehouse app).

How can I have DTS ignore the use of tempdb and just insert the
records directly into my destination table?

Thanks

Kory

How much is it growing by?
How wide is your table?
Do anything fancy?


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

tempdb grows until I run out of space (40G+)

The table has around 200 columns, about 20 of which are subqueries
that do lookups. For example:

Select
B.Col1,
B.Col2,
B.Col3,
(Select key from LookupTable1 L where L.PK = B.PK) Col4,
(Select key from LookupTable2 L where L.PK = B.PK) Col5,
(Select key from LookupTable3 L where L.PK = B.PK) Col6,
C.Col1,
C.Col2
...
From
Table A
Inner Join
Table B
On B.Date >= A.Date
Left Outer Join
Table C
On C.PK = A.PK And A.Date = C.Date


When I select top 5000 * from this view, it returns 5000 records in
about 3 seconds. The entire view will return around 105 million rows
if not constrained.

I would like DTS to insert these records into a table without growing
the tempdb at all (or very little, say every 5000 rows processed).

Suggestions?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Ahhhhhhhhhhh

SQL Server is going to store all the worktables etc in the tempDB for this.
Do you need the subqueries? Can you not add them to the main query?


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote