openquery跨服务器传输数据

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 '自定义别名'