Create Proc SpgeninSertsql (@tablename varchar (256))
AS
Begin
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 (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127)
THEN 'CASE WHEN' NAME 'is NULL TEN' 'NULL' 'ELSE' 'CAST (' Name 'As Varchar)' 'end'
When Xtype in (58, 61)
THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' '' ' ' CAST (' Name ' As Varchar) ' ' '' '' '' ' ' end '
WHEN Xtype in (167)
THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Replace (' Name ', '' '' ',' '' '' '' '' '' ' ' '' '' '' ' ' End '
WHEN Xtype in (231)
THEN 'CASE WHEN' NAME 'Is Null TEN' 'NULL' 'ELSE' '' '' '' '' ' ' 'Replace (' Name ',' '' '', '' '' '' '' '' ' ' '' '' '' 'end'when xtype in (175)
THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Cast (Replace (' Name ', '' '' '" , '' '' '' '' '') AS Char (' Cast As Varchar) ') '' '' '' '' 'End'
When Xtype in (239)
THEN 'CASE WHEN' NAME 'IS NULL THEN' 'NULL' 'ELSE' '' '' '' '' ' ' '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 '] ip (@ SQL, LEN (@SQL) -1) ')' Left (@ Sqlvalues, Len (@sqlvalues) -4 ) ')' 'from' @ TableName
--Print @SQL
EXEC (@SQL)
end
Go