General method to create INSERT scripts for any given table

Question

i would like to make a stored proc or something that will have only the name of the table as a parameter, and will create insert scripts for that table (all records)

this will help me add default records in any given table

thanks

Answer 1

Depends what your end goal is, but I'd suggest trying out the SSMS Tools Pack rather than rolling your own - free SSMS addin, which has lots of handy features. One of the features it gives is you can right click the Tables folder in SSMS -> SSMS Tools -> Generate Insert Statements... - you can then pick which tables you want to generate data INSERT scripts for.

Answer 2

 CREATE procedure [dbo].[GenerateInsert]
 @TableName varchar(50),
 @ShouldGenerateScriptForIdInFirstColumn INT
 as
 Begin

      -- Utility stored Procedure 
      -- Returns the list with inserts (re-create the table with all the data)

      print '-- Swoosh 2004'
      if (@ShouldGenerateScriptForIdInFirstColumn = 1)
      begin
                print ''
                print 'SET IDENTITY_INSERT ' + @TableName + ' ON'
                print 'GO'
                print ''
      end

      declare @strSQL varchar(8000)
      declare @ColumnList varchar(8000)
      declare @ColumnListInOne varchar(8000)
      declare @ColumnName varchar(8000)
      declare @ColumnType int, @myCounter int, @shouldDo int
      declare @ID as integer

      select @ID = id from sysobjects where type = 'U' and name = @TableName
      set @ColumnList = ''
      set @ColumnListInOne = ''
      set @myCounter = 0

      declare ccursor cursor for
                select          Name, Xtype
                from          syscolumns
                where          id = @ID

      open ccursor
      fetch next from ccursor into @ColumnName, @ColumnType
      while @@FETCH_STATUS = 0
      begin
                IF (@ShouldGenerateScriptForIdInFirstColumn = 1)
                BEGIN
                          SET @shouldDo = 1
                END
                ELSE
                BEGIN
                          IF (@myCounter = 0)
                          BEGIN
                                    SET @shouldDo = 0
                          END
                          ELSE
                          BEGIN
                                    SET @shouldDo = 1
                          END
                END

                if (@shouldDo = 1)
                begin
                          Select @ColumnList = @ColumnList + @ColumnName + ', '

                          if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float')) or (@ColumnType = (select xtype from systypes where name = 'decimal')) or (@ColumnType = (select xtype from systypes where name = 'bit'))
                          begin
                                    Select @ColumnListInOne = @ColumnListInOne
                                    Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''{|SWOOSH_NULL_REPLACER|}''),'''''''','''''''''''')))'
                          end
                          else
                          begin
                                    Select @ColumnListInOne = @ColumnListInOne + '''''''''' + ' + '
                                    Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''''),'''''''','''''''''''')))'
                          end

                          if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float')) or (@ColumnType = (select xtype from systypes where name = 'decimal')) or (@ColumnType = (select xtype from systypes where name = 'bit'))
                          begin
                                    Select @ColumnListInOne = @ColumnListInOne
                          end
                          else
                          begin
                                    Select @ColumnListInOne = @ColumnListInOne + ' + ' + ''''''''''
                          end

                          Select @ColumnListInOne = @ColumnListInOne + ' + '', '' + '
                end

                set @myCounter = @myCounter + 1
      fetch next from ccursor into @ColumnName, @ColumnType
      end --while @@FETCH_STATUS = 0
      close ccursor

      deallocate ccursor

      set @ColumnList = Left(@ColumnList,len(@ColumnList)-1)
      set @ColumnListInOne = Left(@ColumnListInOne,len(@ColumnListInOne)-9)

      set @strSQL = ''
      set @strSQL = @strSQL + ' declare @AllValues varchar(8000)'
      set @strSQL = @strSQL + ' set @AllValues  = '''''
      set @strSQL = @strSQL + ' declare ccursor cursor for '
      set @strSQL = @strSQL + '           select ' + @ColumnListInOne + ' from ' + @TableName
      set @strSQL = @strSQL + ' open ccursor '
      set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
      set @strSQL = @strSQL + ' while @@FETCH_STATUS = 0 '
      set @strSQL = @strSQL + ' begin '
      set @strSQL = @strSQL + '          print ''insert into ' + @TableName + ' (' + @ColumnList + ') values ('' + replace(@AllValues, ''{|SWOOSH_NULL_REPLACER|}'', ''NULL'') + '')'' '
      set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
      set @strSQL = @strSQL + ' end '
      set @strSQL = @strSQL + ' close ccursor '
      set @strSQL = @strSQL + ' deallocate ccursor '
      exec (@strSQL )

      if (@ShouldGenerateScriptForIdInFirstColumn = 1)
      begin
                print ''
                print 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
                print 'GO'
      End
 End