-- Prepare script of: INSERT INTO ... SELECT * FROM ...
SET NOCOUNT ON
SELECT 'PRINT ''' + name + '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
	+ CASE WHEN C.id IS NULL
		THEN '' 
		ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] ON' + CHAR(13) + CHAR(10)
		END
	+ 'INSERT INTO MyTempDatabase.dbo.[' + name + ']' + CHAR(13) + CHAR(10) 
	+ 'SELECT * FROM dbo.[' + name + ']' + CHAR(13) + CHAR(10)
	+ CASE WHEN C.id IS NULL
		THEN ''
		ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] OFF' + CHAR(13) + CHAR(10)
		END
	+ 'GO' 
FROM	dbo.sysobjects AS O
	LEFT OUTER JOIN
	(
		SELECT DISTINCT C.id
		FROM	dbo.syscolumns AS C
		WHERE	C.colstat = 1		-- Identity column
	) AS C
		ON C.id = O.id
WHERE	    type = 'U' 
	AND name NOT IN ('dtproperties')
ORDER BY name
SET NOCOUNT OFF

PRINT 'MyTable'
GO
SET IDENTITY_INSERT dbo.[MyTable] ON
INSERT INTO RESTORE_XFER_TEMP.dbo.[MyTable]
SELECT * FROM dbo.[MyTable]
SET IDENTITY_INSERT dbo.[MyTable] OFF
GO

-- Copy PK fields to a temporary table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_PK
-- TRUNCATE TABLE MyRestoreDatabase.dbo.MyBrokenTable
SELECT	[ID]=IDENTITY(int, 1, 1), 
	[IsCopied]=CONVERT(tinyint, 0), 
	MyPK
INTO	MyRestoreDatabase.dbo.TEMP_RESCUE_PK 
FROM	MyBrokenDatabase.dbo.MyBrokenTable 
ORDER BY MyPK

-- If OK then selectively copy data across
-- First Prep. a temp Batch table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECT TOP 1 [ID]=CONVERT(int, NULL), [IsCopied]=CONVERT(bit, 0), MyPK
INTO	MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
GO
--
DECLARE	@intStart	int,
	@intStop	int,
	@intBatchSize	int

-- NOTE: After the first run set these to any "gaps" in the table that you want to fill
SELECT
	@intStart = 1,
	@intBatchSize = 10000,
	@intStop = (SELECT MAX([ID]) FROM MyRestoreDatabase.dbo.TEMP_RESCUE_PK)

SELECT	@intStart = MIN([ID])
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
	AND [ID] >= @intStart

WHILE	@intStart < @intStop
BEGIN
	SET ROWCOUNT @intBatchSize

	-- Isolate batch of Keys into separate table
	TRUNCATE TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
	INSERT INTO MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
	SELECT	T.*
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
	WHERE	    IsCopied = 0
		AND [ID] >= @intStart 
		AND [ID]  < @intStart + @intBatchSize

	-- Attempt to copy matching records, for this batch
	PRINT CONVERT(varchar(20), @intStart)
	INSERT INTO MyRestoreDatabase.dbo.MyBrokenTable
	SELECT	S.*
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH AS T
		LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS D
			ON D.MyPK = T.MyPK
		-- This will try to get the data from the broken table, it may fail!
		JOIN MyBrokenDatabase.dbo.MyBrokenTable AS S
			ON S.MyPK = T.MyPK
	WHERE	D.MyPK IS NULL	-- Belt and braces so as not to copy existing rows

	-- Flag the rows just "Copied"
	UPDATE	U
	SET	IsCopied = 1
	FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS U
	WHERE	IsCopied = 0
		AND [ID] >= @intStart 
		AND [ID]  < @intStart + @intBatchSize

	-- Loop round, until done
	SELECT	@intStart = @intStart + @intBatchSize
END
GO
SET ROWCOUNT 0	-- Turn OFF!!
GO

-- Check amount NOT done:
SELECT	COUNT(*), MIN([ID]), MAX([ID])
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
--	AND [ID] > 123456	-- Optionally count items after a "gap"
--
-- Double check that IsCopied set correctly, and the number of records "lost"
SELECT	COUNT(*), 
	[IsCopied] = SUM(CONVERT(int, IsCopied)), 
	[IsCopied+Record] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
	[IsCopiedNoRecord] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NULL THEN 1 ELSE 0 END),
	[IsNOTCopied] = SUM(CASE WHEN IsCopied = 0THEN 1 ELSE 0 END),
	[IsNOTCopied+Record] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
	[IsNOTCopiedNoRecord] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NULL THEN 1 ELSE 0 END)
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
	LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS C
		ON C.MyPK = T.MyPK
--
-- List of "Lost" records
SELECT	MyPK
FROM	MyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE	    IsCopied = 0
ORDER BY [ID]