IF EXISTS (SELECT * FROM sysobjects WHERE xtype = 'P' AND name = 'spu_GenerateInsert')
BEGIN
     DROP PROCEDURE spu_GenerateInsert
END
GO
 
CREATE PROCEDURE spu_GenerateInsert
     @table varchar(128), -- used to specify the table to generate data for
     @generateGo bit = 0, -- used to allow GO statements to separate the insert statements
     @restriction varchar(1000) = '', -- used to allow the data set to be restricted, no need for the where clause but can use syntax as 'columna = 1'
     @producesingleinsert bit = 0, -- used to switch the ability to produce multiple insert statements (default) or one statement using UNION SELECT
     @debug bit = 0, -- used to allow debugging to be turned on to the stored procedure - in case of queries
     @GenerateOneLinePerColumn bit = 0 -- used to display the columns and data on separate lines
AS
/*******************************************************************
Original Author:  Keith E Kratochvil
This version: Jane Dallaway
Updates at: http://jane.dallaway.com/downloads/SQL/spu_generateInsert.sql
Documentation at: http://jane.dallaway.com/blog/labels/spu_generateinsert.html
Date Created: March 16, 2000
Description: This procedure takes the data from a table and turns it into an
insert statement.
CAUTION!!! If you run this on a large table be prepared to wait a while!
 
Modified:   add a comment here (date, who, comment)
 
Date        Author      Description
~~~~~~~     ~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
07/11/03    Jane  Added a @generateGO parameter to allow selection
07/11/03    Jane  This procedure has an issue with NULLs....
07/11/03    Jane  Deal with Identities
07/11/03    Jane  Wrap all tablenames and columns with [ and ]
15/12/03    Jane  For DateTimes covert as style 1 - to allow for mm/dd/yyy
16/12/03    Jane  Get rid of rowcount
18/12/03    Jane  Prevent single field tables having that field displayed twice
06/07/04    Jane  Large money values get separeted with a comma - so specify convert
                 with 0 rather than 1         
31/05/05    Jane  Added default for GenerateGo and added ability to generate
                 Inserts for selected records based on the restriction
03/01/08    Jane  Updated to cope with text and ntext.  It converts to VARCHAR(8000) to allow quote escaping
                 Also copes with Nulls much better now.
07/01/08    Jane  Now handles guids
07/01/08    Jane  Forced collation to use database_default as was causing an error in some circumstances
15/01/08    Jane  Dave reported issue with image column types as a comment on my blog
                 http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c9038036788352783369
                 So, ensured that all column types either work, or replace with NULL and add warning
23/01/08    Jane  Ignore calculated columns.  Can't migrate the data, so remove from the INSERT
                 Implemented a suggestion from Jon Green - 4R Systems Inc left on my blog post at
                 http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c2055936172890486440
                 to allow a choice of separate insert statements or a single statement using the UNION SELECT syntax.
                 New parameter @producesingleinsert used.  When this is set to 0 it produces separate INSERT statements.
                 When set to 1 it produces a single statment using UNION SELECT
20/08/06 Jane  New parameter @GenerateOneLinePerColumn added as suggested by Christian via comment on my blog post at
                 http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html?showComment=1219174920000#c6992035307857457643
                 Also made all string variables varchar(max).  This means it no longer works on SQL 2000 - but to make it do so is just a case of
                 putting all varchar(max) to varchar(8000)
 
Usage:
     exec spu_GenerateInsert @table ='users', @generateGo=0, @restriction='columna = x', @producesingleinsert=0, @debug=0, @GenerateOneLinePerColumn=0
                                       
 
*******************************************************************/
 
--Variable declarations
DECLARE @InsertStmt varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Fields varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @SelList varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @Data varchar(max) -- change this to be (8000) for SQL Server 2000
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @FldCounter int
DECLARE @TableData varchar(max) -- change this to be (8000) for SQL Server 2000
 
-- added by Jane - 07/11/03
DECLARE @bitIdentity BIT
 
-- added by Jane 03/01/08
DECLARE @bitHasEncounteredText bit
SET @bitHasEncounteredText = 0
 
-- added by Jane 15/01/08
DECLARE @bitHasEncounteredImage bit
SET @bitHasEncounteredImage = 0
DECLARE @bitHasEncounteredBinary bit
SET @bitHasEncounteredBinary = 0
DECLARE @bitHasEncounteredXML int
SET @bitHasEncounteredXML = 0
  
-- added by Jane - 23/01/08
DECLARE @bitInsertStatementPrinted BIT
SET @bitInsertStatementPrinted = 0
 
-- added by Jane - 16/12/03
SET NOCOUNT OFF
 
-- added by Jane - 07/11/03
SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_Name =@table
 
-- added by Jane - 03/01/08
PRINT '-- ** Start of Inserts'
PRINT ''
 
-- added by Jane - 07/11/03
IF @bitIdentity = 1
BEGIN
     PRINT 'SET IDENTITY_INSERT [' + @table + '] ON '
END
 
--initialize some of the variables
-- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
SELECT @InsertStmt = 'INSERT INTO [' + @Table + '] '+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END,
     @Fields = '',
     @Data = '',
     @SelList = 'SELECT ',
     @FldCounter = 0
 
--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the
--field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR
      
     SELECT COLUMN_NAME,
           'IsChar' = CASE
           WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0
           WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1
           WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
           WHEN DATA_TYPE in ('text', 'ntext') THEN 3
           WHEN DATA_TYPE in ('image') THEN 4 -- added by Jane - 15/01/08
           WHEN DATA_TYPE in ('binary', 'varbinary') THEN 5 -- added by Jane - 15/01/08
           WHEN DATA_TYPE in ('sql_variant') THEN 6 -- added by Jane - 15/01/08 - Force to be converted as varchars
           WHEN DATA_TYPE in ('xml') THEN 7 -- added by Jane - 15/01/08
           ELSE 9
     END
     FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
     INNER JOIN syscolumns sc WITH (NOLOCK)
     ON c.COLUMN_NAME = sc.name
     INNER JOIN sysobjects so WITH (NOLOCK)
     ON sc.id = so.id
     AND so.name = c.TABLE_NAME
     WHERE table_name = @table
     AND DATA_TYPE <>'timestamp'
     AND sc.IsComputed = 0
     ORDER BY ORDINAL_POSITION
     FOR READ ONLY
     OPEN CR_Table
 
     FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
 
WHILE (@@fetch_status <> -1)
BEGIN
 
     IF @IsChar = 3
           SET @bitHasEncounteredText = 1           
 
     -- added by Jane - 15/01/08
     IF @IsChar = 4
           SET @bitHasEncounteredImage = 1
     IF @IsChar = 5
           SET @bitHasEncounteredBinary = 1
 
     IF (@@fetch_status <> -2)
     BEGIN
 
           -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
           -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
           -- Special case for first field
           IF @FldCounter = 0
           BEGIN
                 SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                 SELECT @SelList =  CASE @IsChar
                       WHEN 1 THEN @SelList +  ' ISNULL('''''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''') + '''''''' ,''NULL'')  ' + ' COLLATE database_default + '
                       WHEN 2 THEN @SelList +  ' ISNULL('''''''' + CONVERT(varchar(20),[' + @ColName + ']) + '''''''',''NULL'') ' + ' COLLATE database_default + '
                       WHEN 3 THEN @SelList +  ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                       WHEN 4 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                       WHEN 5 THEN @SelList + '''NULL''' + ' COLLATE database_default + '
                       WHEN 6 THEN @SelList +  ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                       WHEN 7 THEN @SelList +  ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                       ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                       END
                 SELECT @FldCounter = @FldCounter + 1
                 SET @SelList = @Sellist
                 FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
           END
 
           -- Updated by Jane - 15/01/08 - cope with xml, image, binary, varbinary etc
           -- Updated by Jane - 03/01/08 to cope with NULL replacements           
           -- Updated by Jane - 03/01/08 to cope with text and ntext - converts to VARCHAR(8000) to allow quote escaping
           -- Updated by Jane - 18/12/03 to prevent single field tables having that field displayed twice
-- Updated by Jane - 20/08/08 to incorporate the @GenerateOneLinePerColumn parameter suggested by Christian
           IF @@fetch_status <> -1
           BEGIN
                 SELECT @Fields =  @Fields + '[' + @ColName + ']' + ', '
                 SELECT @SelList =  CASE @IsChar
                       WHEN 1 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL('''''''' + REPLACE(['+  @ColName + '],'''''''', '''''''''''' ) +  '''''''',''NULL'') ' + ' COLLATE database_default + '
                       WHEN 2 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + '  +  'ISNULL('''''''' + CONVERT(varchar(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
                       WHEN 3 THEN @SelList +  ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13) ' ELSE '' END + ' + ' +  ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''' )+  '''''''',''NULL'')  ' + ' COLLATE database_default + '
                       WHEN 4 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                       WHEN 5 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +  '''NULL''' + ' COLLATE database_default + '
                       WHEN 6 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                       WHEN 7 THEN @SelList + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' +   ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+  @ColName + ']),'''''''', '''''''''''')+  '''''''' ,''NULL'')  '+ ' COLLATE database_default + '
                       ELSE @SelList  + ''',''' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN  ' + CHAR(13) ' ELSE '' END + ' + ' + ' ISNULL(CONVERT(varchar(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + '
                 END  
           END
     END  
 
     FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
 
CLOSE CR_Table
DEALLOCATE CR_Table
 
SELECT @Fields =  SUBSTRING(@Fields, 1,(len(@Fields)-1))
 
SELECT @SelList =  SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @table
 
IF LEN(@restriction) > 0
BEGIN
     SELECT @SelList = @SelList + ' WHERE ' + @restriction
END
 
-- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
SELECT @InsertStmt = @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN REPLACE(@Fields,', ',',' + CHAR(13)) ELSE @Fields END + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')'
 
--for debugging purposes...
IF @Debug = 1
BEGIN
     PRINT '*** DEBUG INFORMATION - THIS IS THE SELECT STATEMENT BEING RUN *** '
     PRINT @sellist
     PRINT '*** END DEBUG ***'
END
 
-- added by Jane - 16/12/03
SET NOCOUNT ON
 
--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement
 
CREATE TABLE #TheData (TableData varchar(8000))
INSERT INTO #TheData (TableData) EXEC (@SelList)
 
--Cursor through the data to generate the INSERT statement / VALUES/SELECT/UNION SELECT clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
 
     WHILE (@@fetch_status <> -1)
     BEGIN
          IF (@@fetch_status <> -2)
          BEGIN
               -- Updated by Jane 23/01/08 after suggestion posted to blog at http://jane.dallaway.com/blog/2007/11/generate-sql-insert-statement-from.html#c2055936172890486440
               IF (@producesingleinsert = 1 )
                    IF (@bitInsertStatementPrinted = 0)
                    BEGIN
                         Print @InsertStmt + char(13) + 'SELECT ' + @TableData
                         SET @bitInsertStatementPrinted = 1
                    END
                    ELSE
                    BEGIN
                         PRINT 'UNION SELECT ' + @TableData
                    END
               ELSE
               BEGIN
                    -- updated by Jane 20/08/08 added one line per column functionality as per Christian's suggestion
                    PRINT @InsertStmt + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + 'VALUES ' + + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + '(' + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + @TableData + CASE WHEN @GenerateOneLinePerColumn = 1 THEN CHAR(13) ELSE '' END + ')' + CHAR(13)
               END
 
               IF @generateGo = 1
               BEGIN
                    PRINT 'GO'
               END
          END
          FETCH NEXT FROM CR_Data INTO @TableData
     END
CLOSE CR_Data
DEALLOCATE CR_Data
 
-- added by Jane - 07/11/03
IF @bitIdentity = 1
BEGIN
     PRINT 'SET IDENTITY_INSERT [' + @table + '] OFF '
END
 
-- added by Jane - 03/01/08
PRINT '-- ** End of Inserts'
 
IF @bitHasEncounteredImage = 1
BEGIN
     PRINT '-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Images are not supported by this script at this time.'''
END
 
-- added by Jane - 15/01/08
IF @bitHasEncounteredBinary = 1
BEGIN
     PRINT '-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL.  You will need to do this by hand.  Binary and VarBinary are not supported by this script at this time.'''
END
 
IF @bitHasEncounteredXML = 1
BEGIN
     PRINT '-- ** WARNING: This will convert any ''xml'' data to be ''varchar(8000)'''
END
 
-- added by Jane - 03/01/08
IF @bitHasEncounteredText = 1
BEGIN
     PRINT '-- ** WARNING: This will convert any ''text'' or ''ntext'' data to be ''varchar(8000)'''
END
 
RETURN (0)
 
GO