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

wapysun

浏览: 21521620 次
性别:
来自: 杭州

最近访客更多访客>>

devcang

hunankeda110

辽东小小

apex53

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 77)
2012-05 ( 587)
2012-04 ( 177)
更多存档...

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
// VERSIONDESCRIPTION
// 2.0Added 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.
// ==============================================================================