TSQL Bulk-Copy Check-Up Sample

BulkTableCopyCheckup.sql

/* NJohnson9402 / natethedba.wordpress.com
Designed for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.

See corresponding blog-post for background. Basically, this is a quick way to "check up on" the status of a
bulk table-copy (data copy from one table to another, potentially across different databases and/or schemas),
by knowing the original start-time, and getting row-counts using `sp_spaceused` for each table (source, destination).
This way, you can get a fairly accurate ETA on when the job will finish, based on how it's done so far.
*/

DECLARE @Sql nvarchar(MAX), @Msg nvarchar(MAX);

DECLARE @StartTime datetime = '$START_TIME$';

DECLARE @DbName_Src sysname, @DbName_Dst sysname
    , @SchemaName_Src sysname, @SchemaName_Dst sysname
    , @TableName_Src sysname, @TableName_Dst sysname
;

SELECT @DbName_Src = '$SOURCE_DB$', @DbName_Dst = '$DEST_DB$'
    , @SchemaName_Src = '$SOURCE_SCHEMA$', @SchemaName_Dst = '$DEST_SCHEMA$'
    , @TableName_Src = '$SOURCE_TABLE$', @TableName_Dst = '$DEST_TABLE$'

IF (OBJECT_ID('tempdb.dbo.#SpaceUsed') IS NOT NULL)
	DROP TABLE #SpaceUsed;

CREATE TABLE #SpaceUsed (id int not null identity(1,1)
    , [name] nvarchar(128), [rows] char(11), [reserved] varchar(18), [data] varchar(18)
    , [index_size] varchar(18), [unused] varchar(18), [measured_at] datetime NOT NULL DEFAULT(GETDATE())
);

--Destination
SET @Sql = QUOTENAME(@DbName_Dst) + '.sys.sp_spaceused ''' + QUOTENAME(@SchemaName_Dst) + '.' + QUOTENAME(@TableName_Dst) + '''';
INSERT #SpaceUsed (name, rows, reserved, data, index_size, unused)
EXEC sp_executesql @Sql

--Source
SET @Sql = QUOTENAME(@DbName_Src) + '.sys.sp_spaceused ''' + QUOTENAME(@SchemaName_Src) + '.' + QUOTENAME(@TableName_Src) + '''';
INSERT #SpaceUsed (name, rows, reserved, data, index_size, unused)
EXEC sp_executesql @Sql

DECLARE @PctComplete real, @ELT_seconds int, @ETT_seconds int, @ETA datetime;

SELECT @PctComplete = CONVERT(real, Curr.[rows]) / CONVERT(real, Total.[rows]) * 100.0
    , @ELT_seconds = DATEDIFF(SECOND, @StartTime, Curr.measured_at)
    , @ETT_seconds = DATEDIFF(SECOND, @StartTime, Curr.measured_at) * CONVERT(real, Total.[rows]) / CONVERT(real, Curr.[rows])
FROM #SpaceUsed Curr, #SpaceUsed Total
WHERE Curr.id=1 AND Total.id=2

SET @ETA = DATEADD(SECOND, @ETT_seconds, @StartTime)

SELECT [Elapsed time (day.hh:mm:ss)] = CONVERT(varchar(10), @ELT_seconds / 86400) + '.' + CONVERT(varchar(30), DATEADD(SECOND, (@ELT_seconds % 86400), 0), 108)
    , [% Complete] = CONVERT(varchar(10), @PctComplete)
    , [# Rows] = CONVERT(varchar(10), Curr.[rows]), [of Total] = CONVERT(varchar(10), Total.[rows])
    , [Est. total time (day.hh:mm:ss)] = CONVERT(varchar(10), @ETT_seconds / 86400) + '.' + CONVERT(varchar(30), DATEADD(SECOND, (@ETT_seconds % 86400), 0), 108)
    , ETA = @ETA, ETA_Friendly = CONVERT(varchar(30), @ETA)
FROM #SpaceUsed Curr, #SpaceUsed Total
WHERE Curr.id=1 AND Total.id=2

Updated to show row-counts in final output (might as well!)