May 28, 2010 at 7:27 am
I am trying to select records from a large table with millions of records and insert the records into another table without filling up tran log 10000 records at a time. Here is the code I am using
SET ROWCOUNT 10000
DECLARE @RCOUNT INT,
SET @RCOUNT = 10000
WHILE @RCOUNT = 10000
BEGIN
BEGIN TRAN
INSERT blanket_Table2
SELECT * FROM blanket_Table
SELECT @RCOUNT = @@rowcount
COMMIT TRAN
CHECKPOINT
END
How do I loop trhrough blank_table untill all the records in it is inserted into blanket_Table2?
May 28, 2010 at 8:03 am
This article is quite informative[/url] for what you want to do.
May 28, 2010 at 8:03 am
Is the table you are insert data into initially empty?
You may want something like this:
declare @rows int;
set @rows = 10000;
while @rows <> 0
begin
insert into dbo.JBMTestA
select top (10000)
t1.*
from
dbo.JBMTest t1
left outer join dbo.JBMTestA t2
on (t1.RowNum = t2.RowNum)
where
t2.RowNum is null;
set @rows = @@rowcount;
checkpoint;
end
May 28, 2010 at 8:39 am
If you can, give this task to DBA.
DBA can switch server into Bulk-logged or Simple Recovery model (if it's currently set to a Full Recovery ) and then use SELECT INTO, which will create and populate new table at once (with all millions of raws) without much logging into transaction log.
May 28, 2010 at 9:24 am
Yes.
May 28, 2010 at 9:30 am
elutin (5/28/2010)
If you can, give this task to DBA.DBA can switch server into Bulk-logged or Simple Recovery model (if it's currently set to a Full Recovery ) and then use SELECT INTO, which will create and populate new table at once (with all millions of raws) without much logging into transaction log.
Hopefully, if the database is using FULL recovery model, the DBA knows better than to switch the recovery model to SIMPLE as that would break the t-log backup chain.
Switching to BULK_LOGGED, running the SELECT INTO, switching back to FULL, running a t-log backup.
Oh, if the database is using FULL recovery model, the checkpoint won't keep the t-log from growing, you'd need to run a t-log backup at that point instead ot the checkpoint.
May 28, 2010 at 10:49 am
Thanks Lynn,
Can you please explain this section of your code?
t1.*
from
dbo.JBMTest t1
left outer join dbo.JBMTestA t2
on (t1.RowNum = t2.RowNum)
where
t2.RowNum is null;
expecially t1.* and t2.
May 28, 2010 at 12:26 pm
I would love to explain my code snippet to you, but first I'd like you to try and explain it to me. To do this you first need to do some reading in Books Online. The two areas to search on are Table Aliases and Left Outer Joins.
I'm asking you to do this to help you learn. I know I understand things better if I do the research first myself and then ask questions for clarification.
May 28, 2010 at 2:12 pm
I will do that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply