思路:
先生成建表的语句,然后自己写代码来实现生成insert语句和执行insert语句的功能。
具体步骤如下:
一、进入pl/sql,将高版本的数据库的表导出(因为表中包含了clob类型的字段,所以用pl/sql导出insert语句的时候没法导出)
二、写代码生成insert语句
首先用select table_name from user_tables语句查询表空间中的所有用户表的表名
SqlLib DoSql = new SqlLib();//SqlLib类自己实现吧,其实就是一个数据库操作类,其中的DoExecuteSqlString方法用来执行sql语句并返回datatable类型的数据
DataTable tnames = DoSql.DoExecuteSqlString("select table_name,num_rows from user_tables");
接下来循环生成所有表的insert语句
1)查询指定表的所有字段以及字段的数据类型
2)查询表的所有数据
for (int i = 0; i < tnames.Rows.Count; i++)//循环所有表
{
//查询指定表的所有字段以及字段的数据类型
DataTable tclos = DoSql.DoExecuteSqlString("select t.table_name,t.column_name,t.data_type,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = '" + SqlLib.GetString(tnames.Rows[i]["table_name"]) + "'");
//查询该表的所有数据
DataTable dbList = DoSql.DoExecuteSqlString("select * from " + SqlLib.GetString(tnames.Rows[i]["table_name"]));
//这里生成insert语句
}
3)生成inert语句
StringBuilder sb = new StringBuilder();
for (int db = 0; db < dbList.Rows.Count; db++)
{
sb.Append("insert into " + SqlLib.GetString(tnames.Rows[i]["table_name"]) + "(");
for (int c = 0; c < tclos.Rows.Count; c++)
{
if (c > 0)
sb.Append("," + SqlLib.GetString(tclos.Rows[c]["column_name"]));
else
sb.Append(SqlLib.GetString(tclos.Rows[c]["column_name"]));
}
sb.Append(")values(");
for (int c = 0; c < tclos.Rows.Count; c++)
{
string dbtype = SqlLib.GetString(tclos.Rows[c]["data_type"]).ToUpper();
switch (dbtype)
{
case "NUMBER":
if (c > 0)
sb.Append("," + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]));
else
sb.Append(SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]));
break;
case "DATE":
if (c > 0)
sb.Append(",to_date('" + SqlLib.GetDateTime(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')");
else
sb.Append("'" + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]) + "'");
break;
case "CLOB":
if (c > 0)
sb.Append(",$$$_____$$$【@" + SqlLib.GetString(tclos.Rows[c]["column_name"]) + "@C@'" + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]) + "'】$$$_____$$$");
else
sb.Append("$$$_____$$$【@" + SqlLib.GetString(tclos.Rows[c]["column_name"]) + "@C@'" + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]) + "'】$$$_____$$$");
break;
//case "":
// break;
default:
if (c > 0)
sb.Append(",$$$_____$$$【@" + SqlLib.GetString(tclos.Rows[c]["column_name"]) + "@V@'" + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]) + "'】$$$_____$$$");
else
sb.Append("$$$_____$$$【@" + SqlLib.GetString(tclos.Rows[c]["column_name"]) + "@V@'" + SqlLib.GetString(dbList.Rows[db][SqlLib.GetString(tclos.Rows[c]["column_name"])]) + "'】$$$_____$$$");
break;
}
}
}