On 8 Feb 2005 09:34:02 -0800, Sri wrote:
I am inserting records into a table - around 1 million records. I want
to do the insert 10000 records at a time using TRAN. My insert
statement is very simple:
BEGIN TRAN T1
Insert INTO TABLE A
(ColA, ColB, ColC)
Select B.TBLBCOLA, B.TBLBCOLB, B.TBLBCOLC
FROM tbltable B
WHERE blah balh...etc.
COMMIT TRAN T1
Any hep or link on the syntax will be helpful!
Hi Sri,
You'll need something like this generic format:
SET ROWCOUNT 10000
WHILE 1 = 1
BEGIN
INSERT INTO TableA (ColA, ColB, ColC)
SELECT b.ColA, b.ColB, b.ColC
FROM TableB AS b
LEFT JOIN TableA AS a
ON a.ColA = b.ColA
AND a.ColB = b.ColB
-- Replace the above with whichever column (or combination of columns)
-- suffices to uniquely identify exactly one row in the data.
WHERE a.ColA IS NULL
-- Use any column from table A that will never contain NULL
-- The left join with a test for NULL in a non NULLable column
-- is a trick; it has the same effect as a NOT EXISTS subquery,
-- but is often quicker
IF @@ROWCOUNT = 0 BREAK
BACKUP LOG MyDatabase
END
SET ROWCOUNT 0
The "backup log" is necessary - else, the logged events will still be kept
in the log. If you don't need recoverability during this operation (i.e.
it suffices to restore an old full or incremental backup and repeat the
mega insert process), you can also use BACKUP LOG MyDatebase WITH NO_LOG.
If your database is in the simple recovery model, you can omit this step.
If you used BACKUP LOG MyDatabase WITH NO_LOG, then you should take a full
or incremental database backup directly after this process is finished.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)