Copy a table minimizing log space

Yes, I know this is a silly situation.
I have an application that uploads several tables to a remote SQL Server
that is part of a web site. The records are uploaded into a dedicated
upload table, then the table they belong in is truncated and the records
copied using
INSERT INTO ... (SELECT ...)
My problem is that the web hosting company is very stingy with log space
and this approach will place the entire table into the log during the
copy. This is failing for the larger tables.
Is there are way for a remote app to copy data from one table to
another, minimizing the use of log space. I have no access to the file
system, so bulk copy is not an option.
Thanks,
++PLSHi
How big are your tables?
What is RECOVERY mode of the database?
Can you divide you inserting into a small transaction ?
INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
OR
SET ROWCOUNT 1000
INSERT INTO Table SELECT * FROM OtherTable
SET ROWCOUNT 0
"pls" <elth-rs1p@.spamex.com> wrote in message
news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
> Yes, I know this is a silly situation.
> I have an application that uploads several tables to a remote SQL Server
> that is part of a web site. The records are uploaded into a dedicated
> upload table, then the table they belong in is truncated and the records
> copied using
> INSERT INTO ... (SELECT ...)
> My problem is that the web hosting company is very stingy with log space
> and this approach will place the entire table into the log during the
> copy. This is failing for the larger tables.
> Is there are way for a remote app to copy data from one table to another,
> minimizing the use of log space. I have no access to the file system, so
> bulk copy is not an option.
> Thanks,
> ++PLS|||RECOVERY mode is simple.
I can't think of any logic that could be in a WHERE clause to divide the
table anything like evenly. I like your second approach (although I
think you meant ...SELECT TOP 1000). But then how do you get to the
second 1000?
++PLS
Uri Dimant wrote:
> Hi
> How big are your tables?
> What is RECOVERY mode of the database?
> Can you divide you inserting into a small transaction ?
> INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
> OR
> SET ROWCOUNT 1000
> INSERT INTO Table SELECT * FROM OtherTable
> SET ROWCOUNT 0
> "pls" <elth-rs1p@.spamex.com> wrote in message
> news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
>
>
>|||pls
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
No, it is exactly SET ROWCOUNT (See in the BOL)
CREATE TABLE #Test
(
col INT NOT NULL PRIMARY KEY
)
--Insert 10 rows
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
INSERT INTO #Test VALUES (3)
INSERT INTO #Test VALUES (4)
INSERT INTO #Test VALUES (5)
INSERT INTO #Test VALUES (6)
INSERT INTO #Test VALUES (7)
INSERT INTO #Test VALUES (8)
INSERT INTO #Test VALUES (9)
INSERT INTO #Test VALUES (10)
SELECT * FROM #Test
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The first five rows were deleted
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The table is empty now
"pls" <elth-rs1p@.spamex.com> wrote in message
news:eTetGT9JGHA.2336@.TK2MSFTNGP12.phx.gbl...
> RECOVERY mode is simple.
> I can't think of any logic that could be in a WHERE clause to divide the
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
> ++PLS
> Uri Dimant wrote: