I've also been doing a lot of scripting to generate procedures, tables, etc... I tweaked my process to accomplish the same task here. I really haven't tested it too much but is another direction. It is SQL2K5 though.
IF OBJECT_ID('dbo.InsDataGenerator') IS NOT NULL
DROP PROCEDURE dbo.InsDataGenerator
GO
CREATE PROCEDURE dbo.InsDataGenerator( @TableId INT )
AS
BEGIN
IF NOT(EXISTS(SELECT * FROM sys.tables WHERE [object_id] = @TableId AND [type] = 'U'))
BEGIN
PRINT 'Table not found'
RETURN
END
DECLARE
@ColumnList VARCHAR(MAX),
@ColumnDataVARCHAR(MAX),
@SqlCmdNVARCHAR(MAX)
SET @ColumnList = ''
SET @ColumnData = ''
SELECT
@ColumnList = @ColumnList + LOWER(c.NAME) + ',',
@ColumnData = @ColumnData +
CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ''''''''' + ' ELSE '' END +
CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN 'CAST(' ELSE '' END +
'RTRIM(' + LOWER(c.NAME) + ')' +
CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN ' AS NVARCHAR(MAX))' ELSE '' END +
CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ' + ''''''''' ELSE '' END +
' + '','' + '
FROM sys.columns c
INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id
WHERE c.[object_id] = @TableId
AND c.is_identity = 0
ORDER BY c.column_id
SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)
SET @ColumnData = SUBSTRING(@ColumnData,1,LEN(@ColumnData)-8)
SET @SqlCmd = 'select ' + @ColumnData + ' from ' + LOWER(OBJECT_NAME(@TableId))
CREATE TABLE #InsDataGenerator( SqlCmd NVARCHAR(MAX))
INSERT INTO #InsDataGenerator
EXEC sp_executesql @SqlCmd
UPDATE #InsDataGenerator SET SqlCmd = 'INSERT INTO ' + OBJECT_NAME(@TableId) + '(' + @ColumnList + ') VALUES(' + SqlCmd + ');'
SELECT * FROM #InsDataGenerator
END
GO
BEGIN
DECLARE @TableId INT
SET @TableId = OBJECT_ID('tbl')
EXEC dbo.InsDataGenerator @TableId
END
GO