根据数据生成 INSERT INTO ... 的 SQL (.Net C#, T-SQL Store Procedure 分别实现)

/*
今天看了 昨天 灵感的 随笔:
http://unruledboy.cnblogs.com/archive/2005/07/18/SmartDBScripts.html

类似的工具也曾写过,今天拿出改 改,重新分享给大家!

1. .Net 1.1 C# 实现
这 仅仅是一个命令行工具,根据指定的 SQL Select 查询:
select * from Table
来生成 insert into ... 的 SQL

生成的 SQL 的 列之间使用 "\t" Tab 分割,
这样生成的 SQL, Copy & Paste 到 Excel 中,利于编辑,很轻松就可以删掉不想要的列

另外还可以控制是否处理 二进制 的大字段!

实现方式使用 DataSet 和 DataReader 两种方法重载!
区别 是:

DataReader.GetDataTypeName(i) 方法可以得到 native 的 SQL Server 的数据类型

DataReader.GetFieldType(i) 方法
DataSet 的 DataTable 的 DataColumn.DataType 属性

都只能得到 .Net 的数据类型!

完整程序下载
https://files.cnblogs.com/Microshaoft/SqlGen.cs.rar

2. T-SQL Store Procedure 实现
有局限性,数据量太大无法生成完整正确的 insert into .... SQL!
*/

1. .Net C#

/*
编译命令行
csc.exe noname1.cs /r:C:\WINdows\Microsoft.NET\Framework\v1.1.4322\System.Data.OracleClient.dll
*/


namespace Microshaoft.ApplicationTest
{
    
using Microshaoft.Util;
    
using Microshaoft.Data;

    
using System;
    
using System.Data;
    
using System.Data.SqlClient;

    
public class ConsoleApplication
    
{
        
static string bs = ""//用于记录上次的位数
        static int j = 0;

        [STAThread] 
//应 ClipBoard 需要
        public 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 * from province";
            
string ConnectionString;
            
//ConnectionString = @"Server=" + S + ";Database=" + D + ";User ID=" + U + ";Password=" + P;
            ConnectionString = @"Server=192.168.1.7\vnet3dev;Database=vnet3center;User ID=vnetdev;password=vnetdev";
            
string s = "";

            
//下面引用 Microsoft SqlHelper 得 到 SqlDataReader
            SqlDataReader x = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql);

            InsertInToSQLGenerator.RowRetrived 
+= new Microshaoft.Util.InsertInToSQLGenerator.RowEventHandler(InsertInToSQLGenerator_RowRetrived);

            System.Console.WriteLine(
"\nuse SqlDataReader :");
            System.Console.Write(
"正在进行第    ");
            s 
= InsertInToSQLGenerator.Generate(x, true);
            
//System.Windows.Forms.Clipboard.SetDataObject(s, true);

            
            
//System.Console.ReadLine();
            sw.WriteLine("\n");

//            j = 0;
//            // 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.Console.Write("正在进行 第    ");
//            bs = "";
//            s = InsertInToSQLGenerator.Generate(ds, true);
            
//System.Windows.Forms.Clipboard.SetDataObject(s, true);

            sw.Close();
            sw 
= null;

            System.Console.WriteLine(
"\nPress any key to continue ");
            
//System.Console.WriteLine("\n" + s);

            System.Console.ReadLine();

        }


        
static System.IO.StreamWriter sw = new System.IO.StreamWriter("Output.sql"false, System.Text.Encoding.GetEncoding("gb2312"));

        
private static void InsertInToSQLGenerator_RowRetrived(string tableName, string fieldNameList, string fieldValueList)
        
{
            sw.WriteLine(
"INSERT INTO \t[" + tableName + "]\t (\t" + fieldNameList + "\t) VALUES (\t" + fieldValueList + "\t)");
            System.Console.Write(bs 
+ "\b\b\b" + +++ " 次," + System.DateTime.Now);
            bs 
= new string('\b', Digits(j) + System.DateTime.Now.ToString().Length + 1); //19 为日期时间字符串长度, 1 是 ","
        }


        
public 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;
        }

    }

}


//================================================================================================================

namespace Microshaoft.Util
{
    
using System.Data;
    
public class InsertInToSQLGenerator
    
{
        
public delegate void RowEventHandler(string TableName,string FieldNameList,string FieldValueList);
        
public static event RowEventHandler RowRetrived;

        
public static string Generate(DataSet ds, bool IgnoreBigColumn)
        
{
            System.Text.StringBuilder sb 
= new System.Text.StringBuilder();
            
foreach (DataTable dt in ds.Tables)
            
{
                
bool b = true;
                
if (sb.Length > 0)
                
{
                    sb.Append(
"\n");
                }

                
string s = "";
                
foreach (DataRow dr in dt.Rows)
                
{
                    
string r = "";
                    
if (sb.Length > 0)
                    
{
                        sb.Append(
"\n");
                    }

                    
foreach (DataColumn dc in dt.Columns)
                    
{
                        
string f = "";
                        
switch (dc.DataType.FullName)
                        
{
                            
case "System.Boolean" :
                                f 
= 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" :
                                f 
= 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" :
                                f 
= dr[dc] == System.DBNull.Value ? "Null" : "'" + dr[dc].ToString().Replace("'""''").Replace("\n""' + CHAR(13) + '"+ "'";
                                
break;
                            
default:
                                
if (!IgnoreBigColumn)
                                
{
                                    f 
= "Null";
                                }

                                
break;
                        }

                        
if (f != "")
                        
{
                            
if (r != "")
                            
{
                                r 
+= "\t,";
                                
if (b)
                                
{
                                    s 
+= "\t,";
                                }

                            }

                            r 
+= f;
                            
if (b)
                            
{
                                s 
+= "[" + dc.ColumnName + "]";
                            }

                        }

                    }

                    
string sql = "INSERT INTO \t[" + dt.TableName + "]\t (\t" + s + "\t) VALUES (\t" + r + "\t)";
                    
if (RowRetrived != null)
                    
{
                        RowRetrived(dt.TableName, s, r);
                    }

                    sb.Append(sql);
                    b 
= false;
                }

            }

            
return sb.ToString();
        }


        
public static string Generate(IDataReader idr, bool IgnoreBigColumn)
        
{
            System.Text.StringBuilder sb 
= new System.Text.StringBuilder();
            
int k = 0;
            
do
            
{
                
bool b = true;
                
if (sb.Length > 0)
                
{
                    sb.Append(
"\n");
                }

                
string s = "";
                
while (idr.Read())
                
{
                    
string r = "";
                    
if (sb.Length > 0)
                    
{
                        sb.Append(
"\n");
                    }

                    
for (int i = 0; i < idr.FieldCount; i++)
                    
{
                        
string f = "";
//                        注释掉的代码只适用 于 SQL Server
//                        switch (idr.GetDataTypeName(i))
//                        {
//                            case "bit" :
//                                f = idr.IsDBNull(i) ? "Null" : ((bool) idr[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" :
//                                f = idr.IsDBNull(i) ? "Null" : idr[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" :
//                                f = idr.IsDBNull(i) ? "Null" : "'" + idr[i].ToString().Replace("'", "''") + "'";
//                                break;
//                            case "nchar" :
//                                goto case "nvarchar";
//                            case "ntext" :
//                                goto case "nvarchar";
//                            case "nvarchar" :
//                                f = idr.IsDBNull(i) ? "Null" : "N'" + idr[i].ToString().Replace("'", "''") + "'";
//                                break;
//                            default:
//                                if (!IgnoreBigColumn)
//                                {
//                                    f = "Null";
//                                }
//                                break;
//                        }
                        switch (idr.GetFieldType(i).FullName)
                        
{
                            
case "System.Boolean" :
                                f 
= idr.IsDBNull(i) ? "Null" : ((bool) idr[i]) ? "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" :
                                f 
= idr.IsDBNull(i) ? "Null" : idr[i].ToString();
                                
break;
                            
case "System.Char" :
                                
goto case "System.String";
                            
case "System.DateTime" :
                                
goto case "System.String";
                            
case "System.String" :
                                f 
= idr.IsDBNull(i) ? "Null" : "'" + idr[i].ToString().Replace("'""''").Replace("\n","' + CHAR(13) + '"+ "'";
                                
break;
                            
default:
                                
if (!IgnoreBigColumn)
                                
{
                                    f 
= "Null";
                                }

                                
break;
                        }

                        
if (f != "")
                        
{
                            
if (r != "")
                            
{
                                r 
+= "\t,";
                                
if (b)
                                
{
                                    s 
+= "\t,";
                                }

                            }

                            r 
+= f;
                            
if (b)
                            
{
                                s 
+= "[" + idr.GetName(i) + "]";
                            }

                        }

                    }

                    b 
= false;
                    
string sql = "INSERT INTO \t[Table" + k + "]\t (\t" + s + "\t) VALUES (\t" + r + "\t)";
                    
if (RowRetrived != null)
                    
{
                        RowRetrived(
"Table" + k, s, r);
                    }

                    sb.Append(sql);
                }

                k 
++;
            }
 while (idr.NextResult());
            
return sb.ToString();
        }

    }

}


//================================================================================================================

// 下面是 Microsoft SqlHelper :

//Data Access Application Block 3.1
// http://www.gotdotnet.com/workspaces/workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431
// SqlHelper.v3.1.cs
//csc.exe SqlHelper.v3.1.cs /t:library /r:C:\WINNT\Microsoft.NET\Framework\v1.1.4322\System.Data.OracleClient.dll

 


2. T-SQL Store Procedure 实现
有局限性,如果字段太多无法生成完整 正确的 Select 'insert into ....' 的 SQL!

create  proc Z_SP_GenInsertSQL (@tablename varchar(256))
as
begin
  
declare @sql varchar(8000)
  
declare @sqlValues varchar(8000)
  
set @sql =' (' + char(9)
  
set @sqlValues = 'values '+ char(9+ '(' + char(9+ '''+'
  
select @sqlValues = @sqlValues + cols + ' + '',' + char(9+ ''' + '  ,@sql = @sql + '[' + name + '],' + CHAR(9)
    
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(@tablenameand autoval is null
        ) T 
  
set @sql ='select ''INSERT INTO ' + CHAR(9+ '['+ @tablename + ']' + CHAR(9+ left(@sql,len(@sql)-2+ char(9+ ''  + CHAR(9+ left(@sqlValues,len(@sqlValues)-5+ char(9+ ')'' from '+@tablename
  
print @sql
  
exec (@sql)
/*
select *
from syscolumns  
where id = object_id('test') and autoval is null
*/

end


2005-08-02 T-SQL Store Procedure 修订为:
完全不受字段数量或字段值影响 而生成正确完整的 INSERT INTO ... SQL

ALTER     procedure Z_SP_GenInsertSQL
(
    
@tablename varchar(256)
    ,
@WhereClause    varchar(1000= 'where 1 = 1'
)
as
begin
/*
usage:
Z_SP_GenInsertSQL '表名','where '
*/

    
declare @sql varchar(8000)
    
declare @sqlValues varchar(8000)
    
set @sql =' ''(''' + char(13+ ','
    
set @sqlValues = ' values ('''+ char(13+ ','
    
select @sqlValues = @sqlValues + cols + ' + '',' + '''' + char(13+ ','
            ,
@sql = @sql + '''[' + name + '],''' + char(13+ ','
    
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 ' + char(13+ '''INSERT INTO '''+ char(13+ ','
                 
+ '''['+ @tablename + ']''' + char(13+ ','
                 
+ left(@sql,len(@sql)-4+ '''' + char(13+ ','')' + left(@sqlValues,len(@sqlValues)-7+ ','')'''
                 
+ char(13+ 'from [' + @tablename + ']'
                 
+ char(13+ @WhereClause
    
--select @sql -- select SQL 被截断
    print @sql -- print SQL 是完整正确的
    exec (@sql)
/*
select *
from syscolumns    
where id = object_id('test') and autoval is null
*/

end
posted @ 2010-11-16 11:07  gllg  阅读(564)  评论(0编辑  收藏  举报