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