create proc spGenInsertSQL@TableName as varchar(100)as--declare @TableName varchar(100)--set @TableName = 'orders'--set @TableName = 'eeducation'DECLARE xCursor CURSOR FORSELECT name,xusertypeFROM syscolumnsWHERE (id = OBJECT_ID(@TableName))declare @F1 varchar(100)declare @F2 integerdeclare @SQL varchar(8000)set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''OPEN xCursorFETCH xCursor into @F1,@F2WHILE @@FETCH_STATUS = 0BEGIN 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,@F2ENDCLOSE xCursorDEALLOCATE xCursorset @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName--print @sqlexec (@sql)
--第二版:2003.03.08go
alter proc SPGenInsertSQL (@tablename varchar(256))asbegin 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))asbegin 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.// ==============================================================================
