We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

USE ajbTest; --your database
SET NOCOUNT ON;

-- Create a target table for testing
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'target') 
BEGIN
	CREATE TABLE [target]
	(
		ID		int,
		Data	varchar(100)
		PRIMARY KEY (ID)
	);
END
GO

/******************************************************************************************
Prepare variables
******************************************************************************************/
-- These would be parameters in a stored proc
DECLARE @sampleDataRows  INT=555000,
	@rowsPerCycle	 INT=50000,
	@logReset	 INT=1

-- startup message
DECLARE @startupMsg	varchar(1000),
		@logsize		 INT=(SELECT size/128 FROM sys.database_files WHERE type=1),
		@maxlogsize		 INT=(SELECT max_size/128 FROM sys.database_files WHERE type=1),
		@transLog		 varchar(100)=(SELECT physical_name FROM sys.database_files WHERE type=1),
		@logName		 varchar(100)=(SELECT name FROM sys.database_files WHERE type=1),
		@dynamicTruncate varchar(200) = 'alter database ' + DB_NAME() + ' set recovery SIMPLE;'

DECLARE @pctLogLeft varchar(6) = 
	CAST(CAST(CAST(@logsize AS float)/CAST(@maxlogsize AS float)*100 AS decimal(10,2)) AS varchar(5))+'%'

SET @startupMsg='Starting large insert into {Your DB}...'+CHAR(13)+CHAR(13)
SET @startupMsg=@startupMsg+'Current Transaction Log stats for '+DB_NAME()+':'+CHAR(13)
SET @startupMsg=@startupMsg+'  Transaction Log File - '+@transLog+CHAR(13)
SET @startupMsg=@startupMsg+'  Transaction Log Capacity - '+CAST(@maxlogsize AS varchar(20))+'MB'+CHAR(13)
SET @startupMsg=@startupMsg+'  Transaction Log Size - '+CAST(@logsize AS varchar(20))+'MB'
SET @startupMsg=@startupMsg+' ('+CAST(@maxlogsize-@logsize AS varchar(20))
SET @startupMsg=@startupMsg+'MB remaining, '+@pctLogLeft+' of capacity)'+CHAR(13)+CHAR(13)
SET @startupMsg=@startupMsg+'Inserting rows into {your table}...'

PRINT @StartupMsg

/******************************************************************************************
Create sample source and target tables, populate with sample data
******************************************************************************************/
DECLARE @sourceData TABLE
(
	ID		INT,
	Data	varchar(100)
	PRIMARY KEY (ID)
);

WITH sampledata (ID,Data) AS
(
	SELECT	1 [ID], 
			SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)+
			SUBSTRING(CONVERT(varchar(40), NEWID()),0,9) [Data]
	UNION ALL
	SELECT	ID+1,
			SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)+
			SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)
	FROM sampledata
	WHERE ID < @sampleDataRows
)
INSERT INTO @sourceData
	SELECT ID, Data FROM sampledata
OPTION (MAXRECURSION 0);

-- Check to see if there is anything to update
IF NOT EXISTS
(
	SELECT a.ID, a.Data FROM @sourceData a
	LEFT JOIN [target] b ON a.ID=b.ID 
	WHERE b.ID IS NULL
)  
	PRINT CHAR(13)+' ... Nothing to update. Yay! (maybe)'

/******************************************************************************************
-- Begin the insert
******************************************************************************************/
BEGIN
	SET ROWCOUNT @rowsPerCycle
	WHILE EXISTS
	(
		SELECT a.ID, a.Data FROM @sourceData a
		LEFT JOIN [target] b ON a.ID = b.ID 
		WHERE b.ID IS NULL
	)  
	BEGIN 
		-- 1st, check if the translog is > 50% capacity. Shrink the log if True. 
		-- This will be checked during each iteration 
		IF @logsize > @maxlogsize/2
		BEGIN
			PRINT	CHAR(13)+'The trans log needs to be shrunk. Shrinking to '+
					CAST(@logReset AS varchar(10))+'MB...'

			IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'FULL'
				BEGIN
					EXEC(@dynamicTruncate);

					DBCC shrinkfile (@logName,@logReset);

					SET @dynamicTruncate = REPLACE(@dynamicTruncate,'SIMPLE','FULL');
					EXEC(@dynamicTruncate);
				END
			ELSE 
				DBCC shrinkfile (@transLog,@logReset);
		END

		BEGIN TRAN 
			INSERT INTO [target]
				SELECT a.ID, a.Data FROM @sourceData a 
				LEFT JOIN [target] b ON a.ID = b.ID 
				WHERE b.ID IS NULL;

			PRINT '  '+CAST(@@ROWCOUNT AS VARCHAR)+' rows inserted.';
		COMMIT;

		BEGIN
			SET @logsize = (SELECT size/128 FROM sys.database_files WHERE type=1)
			PRINT '    '+'Log Size: '+CAST(@logsize AS varchar(20))+'MB'
		END
	END
END

SET ROWCOUNT 0

PRINT CHAR(13)+'All Done.'

IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'FULL'
BEGIN
  EXEC(@dynamicTruncate);
  DBCC shrinkfile (@logName,@logReset);

  SET @dynamicTruncate = REPLACE(@dynamicTruncate,'SIMPLE','FULL');
  EXEC(@dynamicTruncate);
END
ELSE 
  DBCC shrinkfile (@transLog,@logReset);

BEGIN TRAN 
  INSERT INTO [target]
	SELECT a.ID, a.Data FROM @sourceData a 
	LEFT JOIN [target] b ON a.ID = b.ID 
	WHERE b.ID IS NULL;

	PRINT '  '+CAST(@@ROWCOUNT AS VARCHAR)+' rows inserted.';
COMMIT;

Starting large insert into {Your DB}...

Current Transaction Log stats for ajbTest:
  Transaction Log File - E:\SQL_Logs\MSSQL10.MSSQLSERVER\MSSQL\Data\ajbTest_log.ldf
  Transaction Log Capacity - 40MB
  Transaction Log Size - 36MB (4MB remaining, 90.00% of capacity)

Inserting rows into {your table}...

The trans log needs to be shrunk. Shrinking to 1MB...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  50000 rows inserted.
    Log Size: 12MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 23MB

The trans log needs to be shrunk. Shrinking to 1MB...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  50000 rows inserted.
    Log Size: 12MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  50000 rows inserted.
    Log Size: 19MB
  5000 rows inserted.
    Log Size: 19MB

All Done.