Generate an insert script based on the data in a table.

If exists (select * From sysobjects where id = object_id (n' [DBO]. [sp_createdataloadscript] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [sp_createdataloadscript]
Go

Create procedure sp_createdataloadscript
@ Tblname varchar (128)
As
/*
Exec sp_createdataloadscript 'mytable'
*/

Create Table # A (ID int identity (1, 1), coltype int, colname varchar (128 ))

Insert # A (coltype, colname)
Select case when data_type like '% char %' Then 1 else 0 end,
Column_name
From information_schema.columns
Where table_name = @ tblname
Order by ordinal_position

If not exists (select * from #)
Begin
Raiserror ('no columns found for table % s', 16,-1, @ tblname)
Return
End

Declare @ ID int,
@ Maxid int,
@ Cmd1 varchar (7000 ),
@ Cmd2 varchar (7000)

Select @ ID = 0,
@ Maxid = max (ID)
From #

Select @ cmd1 = 'select' Insert '+ @ tblname + '('
Select @ cmd2 = '+ ''select' +'
While @ ID <@ maxid
Begin
Select @ ID = min (ID) from # A where ID> @ ID

Select @ cmd1 = @ cmd1 + colname + ','
From #
Where id = @ ID

Select @ cmd2 = @ cmd2
+ 'Case when' + colname + 'is null'
+ 'Then''' null '''
+ 'Else'
+ Case when coltype = 1 then ''' + '+ colname +' + ''' else' convert (varchar (20), '+ colname +') 'End
+ 'End + '','' +'
From #
Where id = @ ID
End

Select @ cmd1 = left (@ cmd1, Len (@ cmd1)-1) + ')'''
Select @ cmd2 = left (@ cmd2, Len (@ cmd2)-8) + 'from' + @ tblname

Select '/*' + @ cmd1 + @ cmd2 + '*/'

Exec (@ cmd1 + @ cmd2)
Drop table #

Go