"Log file for database is full" running an INSERT statement

I'm getting the following error message


The log file for database 'scratchdb' is full. Back up the transaction log for the database to free up some log space.

when I'm trying to insert new records into my temp table (there is ~1.3 mil rows) to check that it does not exist.

INSERT INTO SCRATCHDB..members_temp
SELECT M.*
FROM SCRATCHDB..members_temp D (NOLOCK) RIGHT OUTER JOIN
(SELECT * FROM PROD..Member (NOLOCK) 
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED  D.MemberNo

Is there a better/efficient way to do this?

Did you try to run this select? I guess it outputs a LOT of records. For example if M table contains 1000 records it outputs 1000 * 1.3M = 1.3 BILLION records (excepts a few with M.MemberNo=D.MemberNo). So in this case increasing a LOG file has no sense. Try to run this query:

INSERT INTO SCRATCHDB..members_temp
SELECT * FROM PROD..Member (NOLOCK) 
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()
AND MemberNo NOT IN (select MemberNo from SCRATCHDB..members_temp (NOLOCK));

StackExchange Database Administrators Q#29164, answer score: 3