根据基本表结构及其数据生成 INSERT ... 的 SQL

create proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
    set @sql =@sql +
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')'
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + char(13) + ''','''
    FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
--print @sql
exec (@sql)

--第二版:2003.03.08
go

alter 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 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
  --print @sql
  exec (@sql)
end

go
--第三版: 2003.3.9

ALTER   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 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,175)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
                  when xtype in (231,239)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
                  else '''NULL'''
                end as Cols,name
           from syscolumns 
          where id = object_id(@tablename) and autoval is null
        ) T
  set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
  print @sql
  exec (@sql)
/*
select *
from syscolumns 
where id = object_id('test') and autoval is null
*/
end

--

C# DataSet SqlDataReader:
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
//using Microsoft.ApplicationBlocks.Data

public class Class1
{
 [STAThread] //应 ClipBoard 需要
 static void Main(string[] args)
 {
    System.Console.WriteLine("pls enter Server:");
    string S = System.Console.ReadLine();
  
    System.Console.WriteLine("pls enter DataBase:");
    string D = System.Console.ReadLine();
  
    System.Console.WriteLine("pls enter User:");
    string U = System.Console.ReadLine();
  
    System.Console.WriteLine("pls enter Password:");
    string P = System.Console.ReadLine();
  
    System.Console.WriteLine("pls enter SQL:");
    string sql = System.Console.ReadLine();
  //

  // DataReader
  //string sql = "select top 2 * from products";
  //sql = "select * from orders /n select * from [order details]";
  string ConnectionString = @"Server=" + S + ";Database=" + D + ";User ID=" + U + ";Password=" + P;

  //下面引用 Microsoft SqlHelper 得到 SqlDataReader
  SqlDataReader x = SqlHelper.ExecuteReader(ConnectionString,System.Data.CommandType.Text, sql);
  System.Console.WriteLine("/nuse SqlDataReader :");
  System.Windows.Forms.Clipboard.SetDataObject(GenerateInsertInToSQLData(x,false),true);

  System.Console.WriteLine("/npls paste (ctrl + v)!");
  System.Console.ReadLine();

  // DataSet
  System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(ConnectionString);
  //下面引用 Microsoft SqlHelper 得到 DataSet
  DataSet ds = SqlHelper.ExecuteDataset(sc, System.Data.CommandType.Text, sql);
  //SqlParameter [] spa = SqlHelperParameterCache.GetSpParameterSet(sc,"zsp_calendar");
  //spa[0].Value = System.DateTime.Parse("1995-09-09");
  //DataSet ds = SqlHelper.ExecuteDataset(sc,CommandType.StoredProcedure,"zsp_calendar",spa);
  System.Console.WriteLine("/nuse DataSet :");
  System.Windows.Forms.Clipboard.SetDataObject(GenerateInsertInToSQLData(ds,true),true);
  System.Console.WriteLine("/npls paste (ctrl + v)!");
  System.Console.ReadLine();

 }
 public static string GenerateInsertInToSQLData(DataSet ds,bool IgnoreBigColumn)
 {
  System.Text.StringBuilder sb = new System.Text.StringBuilder();
  int j = 0;
  string bs = ""; //用于记录上次的位数
  System.Console.Write("/n正在进行第    ");
  foreach (DataTable dt in ds.Tables)
  {
   bool b = true;
   if (sb.Length > 0)
    sb.Append("/n");
   string s = "";
   foreach (DataRow dr in dt.Rows)
   {
    bool B;
    string r = "";
    if (sb.Length > 0)
     sb.Append("/n");
    foreach (DataColumn dc in dt.Columns)
    {
     B = true;
     if (r != "" && !r.EndsWith(","))
      r += ",";
     if (s != "" && !s.EndsWith(",") && b)
      s += ",";
     switch (dc.DataType.FullName)
     {
      case "System.Boolean" :
       r += dr[dc] == System.DBNull.Value ? "null" : ((bool) dr[dc] ) ? "1" : "0";
       break;
      case "System.Decimal" :
       goto case "System.Int32";
      case "System.Double" :
       goto case "System.Int32";
      case "System.Int16" :
       goto case "System.Int32";
      case "System.Int64" :
       goto case "System.Int32";
      case "System.Single" :
       goto case "System.Int32";
      case "System.UInt16" :
       goto case "System.Int32";
      case "System.UInt32" :
       goto case "System.Int32";
      case "System.UInt64" :
       goto case "System.Int32";
      case "System.Int32" :
       r += dr[dc] == System.DBNull.Value ? "null" : dr[dc].ToString();
       break;
      case "System.Char" :
       goto case "System.String";
      case "System.DateTime" :
       goto case "System.String";
      case "System.String" :
       r += dr[dc] == System.DBNull.Value ? "null" : "'" + dr[dc].ToString().Replace("'","''") + "'";
       break;
      default :
       if (IgnoreBigColumn)
       {
        B = false;
       }
       else
       {
        r += "null";
       }
       break;
     }
     if (b && B)
     {
      s += "[" + dc.ColumnName + "]";
     }
    }
    sb.Append("insert into [" + dt.TableName + "] (" + s + ") values (" + r + ")");
    b = false;
    System.Console.Write(bs + "/b/b/b" + ++j + " 次," + System.DateTime.Now);
    bs = new string('/b',Digits(j) + System.DateTime.Now.ToString().Length + 1); //19 为日期时间字符串长度, 1 是 ","
    
   }
  }
  return sb.ToString();
 }
 public static string GenerateInsertInToSQLData(SqlDataReader sdr,bool IgnoreBigColumn)
 {
  System.Text.StringBuilder sb = new System.Text.StringBuilder();
  int j = 0;
  int k = 0;
  string bs = ""; //用于记录上次的位数
  System.Console.Write("/n正在进行第    ");
  do
  {
   bool b = true;
   if (sb.Length > 0)
    sb.Append("/n");
   string s = "";
   while (sdr.Read())
   {
    bool B;
    string r = "";
    if (sb.Length > 0)
     sb.Append("/n");
    for (int i = 0; i < sdr.FieldCount; i++)
    {
     B = true;
     if (r != "" && !r.EndsWith(","))
      r += ","; //数据行
     if (s != "" && !s.EndsWith(",") && b)
      s += ","; //字段列表
     switch (sdr.GetDataTypeName(i))
     {
      case "bit" :
       r += sdr.IsDBNull(i) ? "null" : ((bool) sdr[i]) ? "1" : "0";
       break;
      case "bigint" :
       goto case "int";
      case "smallint" :
       goto case "int";
      case "tinyint" :
       goto case "int";
      case "decimal" :
       goto case "int";
      case "numeric" :
       goto case "int";
      case "money" :
       goto case "int";
      case "smallmoney" :
       goto case "int";
      case "float" :
       goto case "int";
      case "real" :
       goto case "int";
      case "int" :
       r += sdr.IsDBNull(i) ? "null" : sdr[i].ToString();
       break;
      case "datetime" :
       goto case "varchar";
      case "smalldatetime" :
       goto case "varchar";
      case "char" :
       goto case "varchar";
      case "text" :
       goto case "varchar";
      case "varchar" :
       r += sdr.IsDBNull(i) ? "null" : "'" + sdr[i].ToString().Replace("'","''") + "'";
       break;
      case "nchar" :
       goto case "nvarchar";
      case "ntext" :
       goto case "nvarchar";
      case "nvarchar" :
       //sb.Append(sdr.IsDBNull(i) ? "null" : "N'" + sdr[i].ToString().Replace("'","''") + "'");;
       r += sdr.IsDBNull(i) ? "null" : "N'" + sdr[i].ToString().Replace("'","''") + "'";
       break;
      default :
       if (IgnoreBigColumn)
       {
        B = false;
       }
       else
       {
        r += "null";
       }
       break;
     }
     if (b && B)
     {
      s += "[" + sdr.GetName(i) + "]";
     }
    }
    sb.Append("insert into [Table" + k + "] (" + s + ") values (" + r + ")");
    b = false;
    System.Console.Write(bs + "/b/b/b" + ++j + " 次," + System.DateTime.Now);
    bs = new string('/b',Digits(j) + System.DateTime.Now.ToString().Length + 1); //19 为日期时间字符串长度, 1 是 ","
   }
   k ++;
  } while(sdr.NextResult());
  return sb.ToString();
 }

 static int Digits(int n) //数字所占位数
 {
  n = System.Math.Abs(n) ;
  n = n/10;
  int i = 1;
  while (n > 0)
  {
   n = n / 10;
   i++;
  }
  return i;
 }
}

// 下面是 Microsoft SqlHelper :

// ===============================================================================
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and SqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
//   2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================