只要在要 *** 作的表前面添加数据库名相关信息就可以了
如：
select
*
into
test1.dbo.to
from
test2.dbo.from
如果是不同实例的数据库之间的导入就比较麻烦了，
首先需开启Distributed
Transaction
Coordinator服务
然后给你个例子
--以下例子为MSDN上邹建大哥的例子，给你做下参考
/*--同步两个数据库的示例
有数据
srv1.库名..author有字段:id,name,phone,
srv2.库名..author有字段:id,name,telphone,adress
要求：
srv1.库名..author增加记录则srv1.库名..author记录增加
srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新
--*/
--大致的处理步骤
--1.在
srv1
上创建连接服务器,以便在
srv1
中 *** 作
srv2,实现同步
exec
sp_addlinkedserver
'srv2','','SQLOLEDB','srv2的sql实例名或ip'
exec
sp_addlinkedsrvlogin
'srv2','false',null,'用户名','密码'
go
--2.在
srv1
和
srv2
这两台电脑中,启动
msdtc(分布式事务处理服务),并且设置为自动启动
我的电脑--控制面板--管理工具--服务--右键
Distributed
Transaction
Coordinator--属性--启动--并将启动类型设置为自动启动
go
--然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器
--管理
--SQL
Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL
脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
exec
p_process
--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL
Agent服务启动,并设置为自动启动,否则你的作业不会被执行
设置方法:
我的电脑--控制面板--管理工具--服务--右键
SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程
create
proc
p_process
as
--更新修改过的数据
update
b
set
name=i.name,telphone=i.telphone
from
srv2.库名.dbo.author
b,author
i
where
b.id=i.id
and
(b.name
>
i.name
or
b.telphone
>
i.telphone)
--插入新增的数据
insert
srv2.库名.dbo.author(id,name,telphone)
select
id,name,telphone
from
author
i
where
not
exists(
select
*
from
srv2.库名.dbo.author
where
id=i.id)
--删除已经删除的数据(如果需要的话)
delete
b
from
srv2.库名.dbo.author
b
where
not
exists(
select
*
from
author
where
id=b.id)
go
--
质粒上一般包含抗生素抗性基因，转入大肠杆菌中后，一般用含相应抗生素的固体培养基（如2YT培养基、LB培养基等）。重组质粒因其含有抗生素抗性，转入大肠杆菌后，可以在含相应抗生素的培养基中生长。而其他质粒不含抗性，转入大肠杆菌后，无法生长。所以，起到筛选的作用。
望采纳
这个是代码protected void btnImport_Click(object sender, EventArgs e)
{
string filename = string.Empty
try
{
filename = uploadxls(fileexcel)//上传xls文件
//ImportxlsToData(filename)//将xls文件的数据导入数据库
ImportxlsToData1(filename)
if (filename != string.Empty &&System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename)//删除上传的xls文件
}
}
catch (Exception ex)
{
Kit.ExcuteJS("alert('" + ex.Message.Replace("'", "") + "')")
}
}
/// <summary>
/// 动态匹配数据库基表指标
/// </summary>
public void MatchTarget()
{
// 构造导入出错数据DataTable结构
DataTable errorDataTable = new DataTable()
errorDataTable.Columns.Add("出错行数", typeof(string))
SortedList<string, string>list = new SortedList<string, string>()
char[] splitChars = ",".ToCharArray()
string sourceField = hdfSourceField.Value.TrimStart(splitChars)
string targetField = hdfTargetField.Value.TrimStart(splitChars)
if (sourceField.Length >0)
{
string[] sourceFieldList = sourceField.Split(splitChars)
string[] targetFieldList = targetField.Split(splitChars)
for (int i = 0i <sourceFieldList.Lengthi++)
{
list.Add(targetFieldList[i], sourceFieldList[i].Trim())
errorDataTable.Columns.Add(sourceFieldList[i], typeof(string))
}
}
if (list.Count >0)
{
ViewState["TargetMatch"] = list
ViewState["ErrorTable"] = errorDataTable
}
}
/// <summary>
/// 获取DataTable的列名集合
/// </summary>
public List<string>GetTableHead(DataTable dt)
{
List<string>list = new List<string>()
for (int i = 0i <dt.Columns.Counti++)
{
list.Add(dt.Columns[i].ColumnName.Trim())
}
return list
}
/// <summary>
/// 从excel提取数据,并导入到数据库中(导入成功部分，提示失败的记录)
/// </summary>
/// <param name="filename">excel文件路径名</param>
private void ImportxlsToData(string filename)
{
try
{
if (filename == string.Empty)
{
Kit.Alert("excel文件上传失败！")
return
}
MatchTarget()
if (ViewState["TargetMatch"] == null)
{
Kit.Alert("请输入原指标与目标指标对应关系！")
return
}
string oledbconnstring = string.Empty
oledbconnstring = "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" + filename + " Extended Properties='Excel 8.0IMEX=1'"
OleDbConnection oledbconn = null
OleDbDataAdapter oleadmaster = null
DataTable m_tablename = new DataTable()
DataSet ds = new DataSet()
oledbconn = new OleDbConnection(oledbconnstring)
oledbconn.Open()
m_tablename = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
if (m_tablename != null &&m_tablename.Rows.Count >0)
{
m_tablename.TableName = txtSheet.Text.Trim().ToString()
}
string sqlmaster
sqlmaster = " select * from [" + m_tablename.TableName + "＄]"
oleadmaster = new OleDbDataAdapter(sqlmaster, oledbconn)
oleadmaster.Fill(ds, "XLData")
oleadmaster.Dispose()
oledbconn.Close()
oledbconn.Dispose()
// 首先验证填写的原指标是否在要导入的EXCEL表中存在
List<string>columnNameList = GetTableHead(ds.Tables[0])
SortedList<string, string>targetMatchList = (SortedList<string, string>)ViewState["TargetMatch"]
string messageInfo = string.Empty
string targetColumnList = string.Empty
foreach (KeyValuePair<string, string>item in targetMatchList)
{
if (!IsExistsTarget(columnNameList, item.Value))
{
messageInfo += item.Value + ","
}
targetColumnList += "," + item.Key
}
if (messageInfo.Length >0)
{
messageInfo.TrimEnd(',')
messageInfo += "与要导入的EXCEL表格中的列名不同!"
Kit.Alert(messageInfo)
return
}
// 导入数据
targetColumnList = targetColumnList.TrimStart(',')
int successRecord = 0
int failRecord = 0
// 构造导入出错数据DataTable
DataTable errorDataTable = (DataTable)ViewState["ErrorTable"]
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
StringBuilder sb = new StringBuilder()
string sColumn = string.Empty
string sValue = string.Empty
sb.Append("insert into " + TableName + "(")
foreach (KeyValuePair<string, string>item in targetMatchList)
{
sColumn += "," + item.Key
if (dr[item.Value].ToString().Trim().Length >0)
{
sValue += ",'" + dr[item.Value].ToString().Trim().Replace("'", "''") + "'"
}
else
{
sValue += ",null"
}
}
sColumn += ",OperatorId,OperateTime"
sValue += "," + OperatorId + ",getdate()"
sb.Append(sColumn.TrimStart(',') + ")")
sb.Append(" values(" + sValue.TrimStart(',') + ")")
int i = DbHelperSQL.ExecuteSql(sb.ToString())
if (i >0)
{
successRecord += 1
string updateObjectSql = "update SDMS.Object set OperateTime=getdate() where EngName ='" + TableName.Split('.')[1] + "'"
DbHelperSQL.ExecuteSql(updateObjectSql)
}
else
{
failRecord += 1
}
}
catch
{
failRecord += 1
DataRow row = errorDataTable.NewRow()
row["出错行数"] = failRecord + 1
for (int i = 0i <errorDataTable.Columns.Counti++)
{
if (i >0)
{
row[errorDataTable.Columns[i].ColumnName] = dr[errorDataTable.Columns[i].ColumnName]
}
}
errorDataTable.Rows.Add(row)
continue
}
}
string info = "导入成功记录数为：" + successRecord + "。"
if (failRecord >0)
{
info += "导入失败记录数为：" + failRecord + "。"
}
Kit.Alert(info)
if (errorDataTable.Rows.Count >0)
{
gvUnImport.DataSource = errorDataTable
gvUnImport.DataBind()
}
}
catch (Exception ex)
{
Kit.Alert(ex.ToString())
}
}
/// <summary>
/// 从excel提取数据,并导入到数据库中(全部导入成功,失败则全部回滚)
/// </summary>
/// <param name="filename">excel文件路径名</param>
private void ImportxlsToData1(string filename)
{
List<string>sqlList = new List<string>()
try
{
if (filename == string.Empty)
{
Kit.Alert("excel文件上传失败！")
return
}
MatchTarget()
if (ViewState["TargetMatch"] == null)
{
Kit.Alert("请输入原指标与目标指标对应关系！")
return
}
string oledbconnstring = string.Empty
oledbconnstring = "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" + filename + " Extended Properties='Excel 8.0IMEX=1'"
OleDbConnection oledbconn = null
OleDbDataAdapter oleadmaster = null
DataTable m_tablename = new DataTable()
DataSet ds = new DataSet()
oledbconn = new OleDbConnection(oledbconnstring)
oledbconn.Open()
m_tablename = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
if (m_tablename != null &&m_tablename.Rows.Count >0)
{
m_tablename.TableName = txtSheet.Text.Trim().ToString()
}
string sqlmaster
sqlmaster = " select * from [" + m_tablename.TableName + "＄]"
oleadmaster = new OleDbDataAdapter(sqlmaster, oledbconn)
oleadmaster.Fill(ds, "XLData")
oleadmaster.Dispose()
oledbconn.Close()
oledbconn.Dispose()
// 首先验证填写的原指标是否在要导入的EXCEL表中存在
List<string>columnNameList = GetTableHead(ds.Tables[0])
SortedList<string, string>targetMatchList = (SortedList<string, string>)ViewState["TargetMatch"]
string messageInfo = string.Empty
string targetColumnList = string.Empty
foreach (KeyValuePair<string, string>item in targetMatchList)
{
if (!IsExistsTarget(columnNameList, item.Value))
{
messageInfo += item.Value + ","
}
targetColumnList += "," + item.Key
}
if (messageInfo.Length >0)
{
messageInfo.TrimEnd(',')
messageInfo += "与要导入的EXCEL表格中的列名不同!"
Kit.Alert(messageInfo)
return
}
// 导入数据
targetColumnList = targetColumnList.TrimStart(',')
foreach (DataRow dr in ds.Tables[0].Rows)
{
StringBuilder sb = new StringBuilder()
string sColumn = string.Empty
string sValue = string.Empty
sb.Append("insert into " + TableName + "(")
foreach (KeyValuePair<string, string>item in targetMatchList)
{
sColumn += "," + item.Key
if (dr[item.Value].ToString().Trim().Length >0)
{
sValue += ",'" + dr[item.Value].ToString().Trim().Replace("'", "''") + "'"
}
else
{
sValue += ",null"
}
}
sColumn += ",OperatorId,OperateTime"
sValue += "," + OperatorId + ",getdate()"
sb.Append(sColumn.TrimStart(',') + ")")
sb.Append(" values(" + sValue.TrimStart(',') + ")")
sqlList.Add(sb.ToString())
}
string updateObjectSql = "update SDMS.Object set OperateTime=getdate() where EngName ='" + TableName.Split('.')[1] + "'"
sqlList.Add(updateObjectSql)
int errorRow
string errorInfo
int count = DbHelperSQL.ExecuteSqlTran(sqlList, out errorRow, out errorInfo)
string info
if (count >0)
{
info = "导入成功记录数为：" + (count-1) + "。"
}
else
{
info = "导入失败!失败记录行号为：" + errorRow + "。"
info += "失败记录原因为：" + errorInfo
}
Kit.Alert(this, info, true, false)
}
catch (Exception ex)
{
Kit.Alert(ex.ToString())
}
}
/// <summary>
/// 判断填写的原指标是否在要导入的EXCEL表中存在
/// </summary>
/// <param name="columnNameList">表头列名</param>
/// <param name="sourceTargetName">用户填写的原指标名称</param>
public bool IsExistsTarget(List<string>columnNameList, string sourceTargetName)
{
foreach (string columnName in columnNameList)
{
if (sourceTargetName == columnName)
return true
}
return false
}
/// <summary>
/// 上传excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
private string uploadxls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty
string uploadfilepath = string.Empty
string modifyfilename = string.Empty
string fileextend = ""//文件扩展名
int filesize = 0//文件大小
try
{
if (inputfile.Value != string.Empty)
{
//得到文件的大小
filesize = inputfile.PostedFile.ContentLength
if (filesize == 0)
{
throw new Exception("导入的excel文件大小为0，请检查是否正确！")
}
//得到扩展名
fileextend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1)
if (fileextend.ToLower() != "xls")
{
throw new Exception("你选择的文件格式不正确，只能导入excel文件！")
}
//路径
uploadfilepath = Server.MapPath("~/Public/File")
//新文件名
modifyfilename = System.Guid.NewGuid().ToString()
modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1)
//判定是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath)
if (!dir.Exists)
{
dir.Create()
}
orifilename = uploadfilepath + "\\" + modifyfilename
//假如存在,删除文件
if (File.Exists(orifilename))
{
File.Delete(orifilename)
}
// 上传文件
inputfile.PostedFile.SaveAs(orifilename)
}
else
{
throw new Exception("请选择要导入的excel文件!")
}
}
catch (Exception ex)
{
throw ex
}
return orifilename
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）