在oracle升级sql,SQL SERVER升级到ORACLE

利用 SQL自带导出工具导出

将SQL数据库备份，本地还原。将SQL SERVER 字段类型为DATATIME的更改为DATE类型

select 'ALTER TABLE '+b.name+' ALTER column '+a.name+' date null', b.name, a.name columnname,c.name as typename,case when a.is_nullable =0 then 'Not Null' else 'Null' end as nullable,a.*

from sys.columns a , sys.objects b, sys.types c

where a.object_id= b.object_id and a.system_type_id=c.system_type_id

and c.name='datetime' and b.type='u'order by b.name,a.column_id

生成批量SQL语句。

导出的数据没有主键。

批量生成主键 C#代码

private void button2_Click(object sender, EventArgs e)

{

using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection())

{

con.ConnectionString = "server=.;database=database;uid=sa;pwd=";

using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())

{

cmd.CommandText = @"SELECT tbl.name as tablename,

clmns.name,

cik.index_column_id

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key

LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column

where cik.index_column_id>0

order by tbl.name asc";

cmd.Connection = con;

using (System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter())

{

ada.SelectCommand = cmd;

DataTable table = new DataTable();

ada.Fill(table);

Dictionary dics = new Dictionary();

foreach (DataRow row in table.Rows)

{

string tablename = row["tablename"].ToString();

string name = row["name"].ToString();

StringBuilder sb = null;

if (dics.ContainsKey(tablename))

{

sb = dics[tablename];

sb.Append(@",""" + name + @"""");

}

else

{

sb = new StringBuilder();

sb.Append(@"ALTER TABLE ""ORACLE用户名"".""" + tablename + @""" ADD CONSTRAINT ""PK_" + tablename + DateTime.Now.ToString("yyyyMMddHHmmss") + @""" PRIMARY KEY (""" + name + @"""");

dics.Add(tablename, sb);

}

}

StringBuilder sbsql = new StringBuilder();

foreach (System.Collections.Generic.KeyValuePair key in dics)

{

sbsql.AppendLine(key.Value.ToString() + ");");

}

string sql = sbsql.ToString();

}

}

}

}