根据数据生成 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" + ++j + " 次," + 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))asbegin 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(@tablename) and 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')asbegin/*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
