Large Inserts, TempDB Growing

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.
The source tables, and the OLAP table are in different databases.
Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables...
No ORDER BYs... no GROUP BYs
The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.
Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?
any ideas?
thanks
-MarkMark,
Maybe add a WHERE clause and perform the INSERT in multiple parts.
HTH
Jerry
"Mark" <AnonymousPerson12345@.gmail.com> wrote in message
news:1127429078.936480.53900@.o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
>|||Mark wrote:
> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.
David Gugick
Quest Software
www.imceda.com
www.quest.com