Generate "insert" script

CREATE  Procedure dbo.Common_GenInsertStatement 
(
@TableName sysname,
@TrimFlagbit = 0,
@Identity_Handle bit = 0
)
as
/*<summary>**********************************************
#author: xuchangjiang
#date: 2002-07-26
#desc: Generate "insert" script 
#problem: Cannot surpport text column, later i will handle the problem by making a few changes?

#Revision History:
#DATE PROGRAMMER DESCRIPTION
#========== =========== =================================
#2002/07/31 xu.chang-jiang  Handle the problem of No splitter
#2002/08/01 xu.chang-jiang  Handle the problem of IDENTITY
#2002/08/05 xu.chang-jiang  Handle the problem of NULL
#2002/10/17 xu.chang-jiang  Handle the problem of NULL at the end of end.

**********************************************</summary>*/
set nocount on

declare @ColumnName sysname
      , @ColumnType int
      , @ColOrder int
      , @TableID int
      , @ExecStr varchar(4000)
      , @HeadStr varchar(4000)
      , @MaxCol int
      , @ColList bit

declare @TrimHeader varchar(12)
  , @TrimTail varchar(2)
  , @colstat smallint
  , @identity bit
  , @identity_insert_on varchar(100)
  , @identity_insert_off varchar(100)

--We should find out whether the result need to be trimmed.
if @TrimFlag = 1
begin
set @TrimHeader = 'LTRIM(RTRIM('
set @TrimTail = '))'
end
else
begin
set @TrimHeader = ''
set @TrimTail = ''
end

set @ColList = 1
set @identity = 0

set @TableID = object_id(@TableName)
set @HeadStr = '('
set @identity_insert_on  = 'SET IDENTITY_INSERT ' + @TableName + ' ON'
set @identity_insert_off  = 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

select @MaxCol = max(colorder)
  from syscolumns
 where id = @TableID

declare ColumnList cursor scroll for 
 select name, xtype, colorder, colstat
   from syscolumns
  where id = @TableID
  order by colorder

open ColumnList

fetch first
 from ColumnList 
 into @ColumnName, @ColumnType, @ColOrder, @colstat

while @@fetch_status <> -1
begin

   if @colstat = 1 and @Identity_Handle = 1
  set @identity = 1

   if not(@colstat = 1 and @Identity_Handle = 0)
   begin
   set @HeadStr = @HeadStr + 
                  case when @ColOrder < @MaxCol then @ColumnName + ', '
                       else @ColumnName + ')'
                        end
   end

   fetch next 
    from ColumnList 
    into @ColumnName, @ColumnType, @ColOrder, @colstat
end

if @ColList = 1
   set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' ' + @HeadStr + ' VALUES ('' + '
else
   set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '

fetch first
 from ColumnList 
 into @ColumnName, @ColumnType, @ColOrder, @colstat

while @@fetch_status <> -1
begin
   
   if not(@colstat = 1 and @Identity_Handle = 0)
   begin
   set @ExecStr = @ExecStr + 
                  case when @ColumnType in (48,52,56,59,62,104,106,108,127) then 'convert(varchar, ' + @ColumnName + ')' 
                       when @ColumnType in (61) then ''''''''' + replace(convert(varchar, ' + @ColumnName + ', 120), ''.'', '''') + ''''''''' 
                       else '''N'' + '''''''' + ' +@TrimHeader+'replace(' + @ColumnName + ', '''''''', '''''''''''')'+@TrimTail+' + '''''''''
                        end +
                  case when @ColOrder < @MaxCol then ','+ ''','' as Splittor'+', '
                       else ',' + ''')'' as Tail'+' FROM ' + @TableName
                        end
    end

   fetch next 
    from ColumnList 
    into @ColumnName, @ColumnType, @ColOrder, @colstat
end

close ColumnList
deallocate ColumnList

if @identity = 1
select @identity_insert_on

exec (@Execstr)

if @identity = 1
select @identity_insert_off



return