SQL stored procedure for Automatically Generating INSERT statements

This is a stored procedure. By executing this stored procedure, the data in the specified data table is guided into an Insert statement without CREATEPROCEDURE # usp_GenInsertSql (@ tablenameVARCHAR (256) ASBEGINDECLARE @ sqlVARCHAR (MAX) DECLARE @ sqlValuesVARCHAR (MAX) SET @ SQL (SET @ sqlVa

This is a stored PROCEDURE. By executing this stored PROCEDURE, the data in the specified data table is guided into an Insert statement without the create procedure # usp_GenInsertSql (@ tablename VARCHAR (256 )) as begin declare @ SQL VARCHAR (MAX) DECLARE @ sqlValues VARCHAR (MAX) SET @ SQL = '('set @ sqlVa

This is a stored procedure. By executing this stored procedure, the data in the specified data table is imported into the Insert statement form. <无>

 CREATE PROCEDURE #usp_GenInsertSql (@tablename VARCHAR(256))AS  BEGIN      DECLARE @sql VARCHAR(MAX)      DECLARE @sqlValues VARCHAR(MAX)      SET @sql =' ('      SET @sqlValues = 'values (''+'      SELECT @sqlValues = @sqlValues + cols + ' + '','' + ',             @sql = @sql + QUOTENAME(name) + ','      FROM   (SELECT CASE                       WHEN xtype IN ( 48, 52, 56, 59,                                       60, 62, 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 = 167  THEN 'case when ' + name                                                  +                       ' is null then ''NULL'' else '                                                  + ''''''''' + ' + 'replace(' +                                                  name                                                  + ','''''''','''''''''''')' +                                                  '+'''''''''                                                  + ' end'                       WHEN xtype = 231  THEN 'case when ' + name                                                  +                       ' is null then ''NULL'' else '                                                  + '''N'''''' + ' + 'replace('                                                  +                                                  name                                                  + ','''''''','''''''''''')' +                                                  '+'''''''''                                                  + ' end'                       WHEN xtype = 175  THEN                       'case when ' + name                       + ' is null then ''NULL'' else '                       + ''''''''' + ' + 'cast(replace(' +                       name                       + ','''''''','''''''''''') as Char('                       + Cast(length AS VARCHAR) +                       '))+'''''''''                       + ' end'                       WHEN xtype = 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)  ENDgoEXEC #usp_GenInsertSql  test