It shouldn't need to cursor through the fields either.

Below is a version I'd used in the past. I've just updated it for SQL 2005 and 2008. It's not got lot's of bells and whistles, but supports multiple tables/schemas (using wildcard in table and schema name) and as far my testing goes, supports all the new data types (some additional enhancement may be required to handle sql_variant and UDT's).

It also uses a cursor, but only to iterate over all the tables that match the parameter definitions.

Oh, and it supports scripting data from views.

CREATE PROCEDURE [dbo].[usp_generate_inserts]

/***

$One-Liner

Generates an insert script for the specified object(s)

$Detail

This stored procedure will generate an INSERTS script for the data contained in

a table or view. It supports wildcards for both table anme and schema parameters.

$Example

script data from [dbo].[test]

EXECUTE usp_generate_inserts 'test'

script data from all tables that begin with dim in the warehouse schema

EXECUTE usp_generate_inserts 'dim%', 'warehouse'

$Support

SQL Server 2005 and 2008

$Created By Date

=====================================================

Adam Tappis 31 Dec 2008

***/

(

@i_table_name NVARCHAR(255) = NULL

, @i_schema_name NVARCHAR(255) = 'dbo'

)

AS

DECLARE

@vsSQL NVARCHAR(MAX)

, @vsCols NVARCHAR(MAX)

, @vsTableName SYSNAME

, @maxID INT

CREATE TABLE #tmp

( id INT IDENTITY

, [--sqltext] NVARCHAR(MAX))

CREATE TABLE #tmp2

( id INT IDENTITY

, [--sqltext] NVARCHAR(MAX))

SET NOCOUNT ON

DECLARE csrTables CURSOR FOR

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME([name])

FROM sys.objects

WHERE type IN ('U', 'V')

AND [name] LIKE ISNULL(@i_table_name, [name])

AND SCHEMA_NAME(schema_id) LIKE ISNULL(@i_schema_name, SCHEMA_NAME(schema_id))

ORDER BY [name]

OPEN csrTables

FETCH NEXT

FROM csrTables

INTO @vsTableName

WHILE @@fetch_status = 0

BEGIN

SELECT @vsSQL = ''

, @vsCols = ''

SELECT @vsSQL = @vsSQL

+ CASE

-- ignore timestamp columns

WHEN st.name IN ('timestamp') THEN ''

-- handle binary types

WHEN st.name IN ('image','binary','varbinary','geography','geometry') THEN

'ISNULL(sys.fn_varbintohexstr(CAST(' + sc.name + ' AS VARBINARY(MAX))),

''NULL'')+'',''+'

-- handle GUID columns

WHEN st.name in ('uniqueidentifier') THEN

'ISNULL('''''''' + CAST(' + sc.name + ' AS CHAR(36)) +

'''''''',''NULL'')+'',''+'

-- handle date and time types

WHEN st.name LIKE '%date%' OR st.name LIKE '%time%' THEN

'ISNULL('''''''' + CONVERT(VARCHAR(50),' + sc.name + ',113) +

'''''''',''NULL'')+'',''+'

-- handle string types

WHEN st.name in ('sql_variant','varchar','char','nvarchar','nchar','sysname','xml',

'text', 'ntext','hierarchyid') THEN

'ISNULL(''' + CASE LEFT(st.name,1) WHEN 'n' THEN 'N' ELSE '' END + '''''''+'

+ 'REPLACE(CAST(' + sc.name + ' AS

NVARCHAR(MAX)),'''''''','''''''''''')+'''''''',''NULL'')+'',''+'

-- numeric types

ELSE 'ISNULL(CAST(' + sc.name + ' AS VARCHAR(MAX)),''NULL'')+'',''+'

END

FROM sys.columns sc

JOIN sys.types st

ON sc.user_type_id = st.user_type_id

WHERE sc.object_id = OBJECT_ID(@vsTableName)

ORDER BY column_id

SELECT @vsCols = @vsCols + sc.name + ','

FROM sys.columns sc

JOIN sys.types st

ON sc.user_type_id = st.user_type_id

WHERE sc.object_id = OBJECT_ID(@vsTableName)

AND st.name <> 'timestamp'

ORDER BY column_id

SELECT @vsSQL = STUFF(@vsSQL,LEN(@vsSQL) - 2, 3, '''')

, @vsCols = STUFF(@vsCols,LEN(@vsCols), 1, '')

INSERT #tmp

EXEC ('SELECT ' + @vsSQL + ' FROM ' + @vsTableName)

-- Check if any rows were present

IF (SELECT COUNT(*) FROM #tmp) > 0

BEGIN

SELECT @maxID = MAX(id)

FROM #tmp

UPDATE #tmp

SET [--sqltext] = 'SELECT ' + SUBSTRING([--sqltext],1,DATALENGTH([--sqltext])-1)

+ CASE id WHEN @maxID THEN '' ELSE CHAR(13) + CHAR(10) + ' UNION ALL' END

INSERT #tmp2

SELECT CHAR(13) + CHAR(10) + 'DELETE FROM ' + @vsTableName + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

IF (SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@vsTableName) AND is_identity = 1) > 0

BEGIN

INSERT #tmp2

SELECT CHAR(13) + CHAR(10) + 'SET IDENTITY_INSERT ' + @vsTableName + ' ON'

END

INSERT #tmp2

SELECT CHAR(13) + CHAR(10) + 'INSERT ' + @vsTableName + '(' + @vsCols + ')'

INSERT #tmp2

SELECT [--sqltext]

FROM #tmp

IF (SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@vsTableName) AND is_identity = 1) > 0

BEGIN

INSERT #tmp2

SELECT CHAR(13) + CHAR(10) + 'SET IDENTITY_INSERT ' + @vsTableName + ' OFF'

END

INSERT #tmp2

SELECT 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'UPDATE STATISTICS ' + @vsTableName + CHAR(13) +

CHAR(10) + 'GO'

DELETE #tmp

END

ELSE

BEGIN

INSERT #tmp2

SELECT '-- Now rows in table ' + @vsTableName

END

FETCH NEXT

FROM csrTables

INTO @vsTableName

END

CLOSE csrTables

DEALLOCATE csrTables

UPDATE #tmp2

SET [--sqltext] = SUBSTRING([--sqltext], 1, CHARINDEX(',)', [--sqltext]) - 1)

+ ',NULL)'

WHERE CHARINDEX(',)', [--sqltext]) <> 0

UPDATE #tmp2

SET [--sqltext] = REPLACE([--sqltext], ',''''', ',NULL')

WHERE CHARINDEX(',''''', [--sqltext]) <> 0

UPDATE #tmp2

SET [--sqltext] = REPLACE([--sqltext], '(''''', ',NULL')

WHERE CHARINDEX('(''''', [--sqltext]) <> 0

SELECT [--sqltext]

FROM #tmp2

ORDER BY id

SET NOCOUNT OFF

GO