openquery跨服务器传输数据

2019/7/23 7:25:25 人评论 次浏览 分类:学习教程

原文链接:http://www.cnblogs.com/insist/archive/2012/04/13/2445611.html

openquery:  

/*不同服务器数据库之间的数据操作*/

不同数据库之间复制表的数据的方法:

当表目标表存在时:

insert into 目的数据库..表 select * from 源数据库..表  

当目标表不存在时:

select * into 目的数据库..表 from 源数据库..表

当目标存在时,远程向本地插入表数据,如果目标不存在则出错
insert into omdLog select * from OPENQUERY(数据库名,'select * from 数据库名.dbo.表明 where 表字段< ''2008-01-01 00:00:00''') 
当目标不存在时,远程在本地创建表数据,如果目标存在则出错
select * into table_name from 
OPENQUERY(test ,'select * from 数据库名.dbo.表名 where LogDate < ''2009-01-01 00:00:00''') 
insert into 表名 openquery(test,  'SELECT *  FROM 数据库.dbo.表名 ') 
select * from 表名 where 表字段 < @变量

查询:SELECT * FROM OPENQUERY(TEST, 'SELECT * FROM tableName')
写入:INSERT INTO OPENQUERY(TEST, 'SELECT * FROM tableName WHERE 1>1') VALUES(3,6,'ANDY')
更新:UPDATE OPENQUERY(TEST, 'SELECT * FROM tableName') SET role_id = 'ForTest' WHERE id = 3
删除:DELETE OPENQUERY(TEST, 'SELECT * FROM tableName') WHERE id IN (1,3) 

 

实例:

--建立连接服务器
exec sp_addlinkedserver '自定义别名', '','SQLOLEDB', '目标服务器IP地址'
exec sp_addlinkedsrvlogin '自定义别名','false',null,'sa','密码!'

--打开自动回滚,出错时会自动回滚当前事务
--set xact_abort on
--开始事务
--begin tran

Use Test
Go

--日志
--Select * From omdLog
Set IDENTITY_INSERT omdLog ON
Insert Into omdLog(id,LogName,LogDate,UserName,RolesName,IP,Description,logType,storeID,repairListID,ItemID,storeCode,itemStatus,itemPlanDate,itemPlanTime,facilityType,logAction,belongFileID,moduleID)
Select * From
OPENQUERY(test ,'select * from 数据库名.dbo.omdLog where LogDate < ''2009-01-01 00:00:00''')
Set IDENTITY_INSERT omdLog OFF

Delete From OPENQUERY(test ,'select * from 数据库名.dbo.omdLog where LogDate < ''2009-01-01 00:00:00''')

--附件- 工单
--Select * From omdAttachment
Set IDENTITY_INSERT omdAttachment ON
Insert Into omdAttachment(attachID,belongType,storeID,ItemID,description,[fileName],filePath,fileReName,uploadDate,uploader,orderNo,uploadUserName,storeCode,fromType,fileActionType,belongFileID,moduleID,uploaderRoleID,fileType)
Select * From
OPENQUERY(test ,'select * from 数据库名.dbo.omdAttachment Where ItemID in
(
Select SheetID From 数据库名.dbo.omdRepairSheet
Where ItemID in
(
Select ItemID From 数据库名.dbo.omdRepairItem
Where OrderID in (Select OrderID From 数据库名.dbo.omdRepairOrder Where OrderDate > ''2011-04-01 00:00:00'')
)
)
And BelongType = 3')

Delete From OPENQUERY(test ,'select * from 数据库名.dbo.omdAttachment Where ItemID in
(
Select SheetID From 数据库名.dbo.omdRepairSheet
Where ItemID in
(
Select ItemID From 数据库名.dbo.omdRepairItem
Where OrderID in (Select OrderID From 数据库名.dbo.omdRepairOrder Where OrderDate < ''2009-01-01 00:00:00'')
)
)
And BelongType = 3')

Set IDENTITY_INSERT omdAttachment OFF

--commit tran

exec sp_droplinkedsrvlogin '自定义别名','sa'
exec sp_dropserver '自定义别名'

 

 

转载于:https://www.cnblogs.com/insist/archive/2012/04/13/2445611.html

相关资讯

    暂无相关的资讯...

共有访客发表了评论 网友评论

验证码: 看不清楚?
    -->