SQL generates the insert language name based on the table structure and data.

Declare @ Tablename Varchar (256)

Set@ Tablename = 'yp_drugname'

Declare @ SQL Varchar (8000)
Declare @ Sqlvalues Varchar (8000)
Set @ SQL = '('
Set @ Sqlvalues = 'values (''+'
Select @ Sqlvalues = @ sqlvalues + Cols + '+ '','' + ',@ SQL = @ SQL + '[' + [ Name ] + '],'
From
( Select Case
When Xtype In (104,106,108,122,127)
Then 'Case when' + [ Name ] + 'Is null then' 'null' 'else' + 'Cast ('+ [ Name ] + 'As varchar) '+ 'end'
When Xtype In (58,61)
Then 'Case when' + [ Name ] + 'Is null then' 'null' 'else' + ''' + 'cast (' + [ Name ] + 'As varchar) '+ ''' + 'end'
When Xtype In (167)
Then 'Case when' + [ Name ] + 'Is null then' 'null' 'else' + ''' + 'Replace (' + [ Name ] + ','''''''','''''''''''') '+ ''' + 'End'
When Xtype In (231)
Then 'Case when' + [ Name ] + 'Is null then' 'null' 'else' + '''n' ''' + '+ 'replace (' + [ Name ] + ','''''''','''''''''''') '+ ''' + 'End'
When Xtype In (175)
Then 'Case when' + [ Name ] + 'Is null then'' 'null''' else '+ ''' +' cast (replace ('+ [ Name ] + ', ''', ''') As char (' + Cast ( Length As Varchar ) + ') + ''' + 'End'
When Xtype In (239)
Then 'Case when' + [ Name ] + 'Is null then' 'null' 'else' + '''n' ''' + '+ 'Cast (replace (' + [ Name ] + ', ''', ''') As char (' + Cast ( Length As Varchar ) + ') + ''' + 'End' Else '''Null '''
End As Cols ,[ Name ] From Syscolumns Where Id = object_id (@ tablename)
) T
Set @ SQL = 'Select' insert into ['+ @ tablename +'] '+ Left (@ SQL , Len (@ SQL )-1) + ')' + Left (@ Sqlvalues, Len (@ sqlvalues)-4) + ') ''from' + @ tablename
Exec (@ SQL )

Publish by note