December 18, 2003 at 1:58 pm
Hello,
I have a SQL Server 2000 table with a large number of records in it, and I need to insert all of these records into another SQL Server 2000 table within the same database. When I attempt an INSERT...SELECT to insert the data into the other table, I fill up the Transaction Log.
I could do a Bulk Insert if I were to export the data to a text file first, but is there something else I could do, maybe within DTS, where I could Insert the data without having to change the Recovery Model, and avoid a Bulk Insert from a text file?
Thanks for your help!
CSDunn
December 18, 2003 at 2:14 pm
I believe that select into is a non-logged operation. Of course if you do a select into or bcp, you're breaking the transaction chain, so you'll have to do a full dump.
December 18, 2003 at 2:39 pm
quote:
I believe that select into is a non-logged operation.
I think SELECT INTO creates a new table. I need to use an existing table that already has the indexes build on it. I tried to build a Unique index on the copy of the table when it already had the data in it, and this also filled up the log.
CSDunn
December 18, 2003 at 3:59 pm
quote:
I tried to build a Unique index on the copy of the table when it already had the data in it, and this also filled up the log.
Ha! That was my next thought! Drop all the indexes and rebuild. Oh well.
If the source table has a PK, you could use a cursor <shudder> to grab a few
thousand records at a time with your INSERT...SELECT, perhaps even explicitly truncating the log when you're done with each iteration of the cursor.
SJTerrill
December 18, 2003 at 6:41 pm
Use the same technique we do when deleting large amounts of data...
DECLARE @NumRows int
, @iErr int
, @PK ...
-- look up the first PK to start the insert on here..
SELECT @PK = MIN(PK)
FROM Foo
SET @NumRows = 5000
SET ROWCOUNT @NumRows
BEGIN TRAN
-- do the first insert
INSERT INTO FOO2....
SELECT...
WHERE PKField >= @PK
ORDER BY PK
SELECT @NumRows = @@ROWCOUNT, @iErr = @@ERROR
-- do errror check
IF @iErr != 0
BEGIN
GOTO FINIS -- and report error...
END
ELSE COMMIT TRAN
-- Now you want to look up the last PK inserted to use for the next insert..
SELECT @PK = MAX(PK)
FROM Foo2
WHILE @NumRows > 0
BEGIN
BEGIN TRAN
INSERT INTO FOO2....
SELECT...
WHERE PKField > @PK
ORDER BY PK
SELECT @NumRows = @@ROWCOUNT, @iErr = @@ERROR
-- do errror check
IF @iErr != 0
BEGIN
GOTO FINIS -- and report error...
END
ELSE
BEGIN
COMMIT TRAN
BACKUP LOG WITH TRUNCATE_ONLY
END
-- Now you want to look up the last PK inserted to use for the next insert..
SELECT @PK = MAX(PK)
FROM Foo2
END
FINIS: --make sure to clean up and report any errors here...
SET ROWCOUNT 0
Please note that I would first backup the database before doing this script incase you need to roll the database back to the state before the script was run. Since you need to get batches of records to insert you will need to be able to order the select statement in such a way as to only grab the data you want to do the insert on.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply