when t.name = 'datetime' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(23),['+c.name+ '],121)'+ '+'''''''''+' End'

when t.name = 'smalldatetime' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(23),['+c.name+ '],120)'+ '+'''''''''+' End'

when t.name in('int','smallint','tinyint')Then 'case When ['+ c.name +'] Is Null Then ''null'' Else ' + 'cast(['+ c.name + '] As Varchar)'+' End'

when t.name = 'uniqueidentifier' Then'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(36),['+c.name+ '])'+ '+'''''''''+' End'

--whent.name='ntext' Then 'case When ['+c.name+'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'replace(['+ c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

else 'case When ['+ c.name +'] Is Null Then ''null'' Else ' +'''N'''''' + ' + 'cast(['+ c.name + '] As nvarchar(4000))'+ '+'''''''''+' End'

End as col

,c.nameas name

,c.column_idas column_id

,c.is_identityas is_identity

from sys.columnsc

inner join sys.typest on c.system_type_id= t.system_type_idand c.user_type_id= t.user_type_id

inner join sys.objectso on o.object_id= c.object_id

where o.type = 'U'

and o.name= @table_name)t

order bycolumn_id

set @sql ='select ''set identity_insert'+@table_full_name+' on Insert Into '+ @table_full_name + Left(@Sql,Len(@Sql)-1)+') ' + Left(@Sqlvalues,Len(@Sqlvalues)-4) + ')'' From '+@table_full_name

exec(@sql)

FETCH NEXTFROM S_Cursor INTO @table_name,@table_full_name

END

CLOSE S_Cursor

DEALLOCATE S_Cursor