MS SQL Server - select into

Author

select into

Hassan

2005-10-20, 8:23 pm

Im doing a "select into" a table from another table thats probably around
200GB without indexes

So the questions is,

Will my Tlog grow ?
Will tempdb grow ?

Dan Guzman

2005-10-20, 8:23 pm

> Will my Tlog grow ?

It depends on your database recovery model. SELECT INTO is fully logged in
the FULL recovery model so log space requirements will be substantial. The
statement is minimally logged in the BULK LOGGED and SIMPLE models so log
space requirements are small.

> Will tempdb grow ?

Not as a result of the SELECT into operation. Tempdb space may be needed if
you have a complex query plan that includes work tables or sort operators.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hassan" <hassanboy@hotmail.com> wrote in message
news:uYsBlCE1FHA.3780@TK2MSFTNGP12.phx.gbl...
> Im doing a "select into" a table from another table thats probably around
> 200GB without indexes
>
> So the questions is,
>
> Will my Tlog grow ?
> Will tempdb grow ?
>

Jerry Spivey

2005-10-20, 8:23 pm

Hassan,

You might consider using a bulk copy operation instead.

See:

Optimizing Bulk Copy Performance
http://msdn.microsoft.com/library/d...tun_1a_5gyt.asp
and
Logged and NonLogged Bulk Copies
http://msdn.microsoft.com/library/d..._6_040_7zn4.asp
and
Logged and Minimally Logged Bulk Copy Operations
http://msdn.microsoft.com/library/d...pt_bcp_9esz.asp

HTH

Jerry

"Hassan" <hassanboy@hotmail.com> wrote in message
news:uYsBlCE1FHA.3780@TK2MSFTNGP12.phx.gbl...
> Im doing a "select into" a table from another table thats probably around
> 200GB without indexes
>
> So the questions is,
>
> Will my Tlog grow ?
> Will tempdb grow ?
>

Hari Prasad

2005-10-20, 8:23 pm

Hassan,

As jerry mentioned go for Bulk copy operation with BatchSize. So based on
the batchsize the transaction will be commited and if your recovery model
is SIMPLE the Transaction log will not grow.

Thanks
Hari
SQL Server MVP

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:%23$bZ4DF1FHA.1028@TK2MSFTNGP12.phx.gbl...
> Hassan,
>
> You might consider using a bulk copy operation instead.
>
> See:
>
> Optimizing Bulk Copy Performance
> http://msdn.microsoft.com/library/d...tun_1a_5gyt.asp
> and
> Logged and NonLogged Bulk Copies
> http://msdn.microsoft.com/library/d..._6_040_7zn4.asp
> and
> Logged and Minimally Logged Bulk Copy Operations
> http://msdn.microsoft.com/library/d...pt_bcp_9esz.asp
>
> HTH
>
> Jerry
>
> "Hassan" <hassanboy@hotmail.com> wrote in message
> news:uYsBlCE1FHA.3780@TK2MSFTNGP12.phx.gbl...
>
>

Sponsored Links