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 TestGo

--日志--Select * From omdLogSet IDENTITY_INSERT omdLog ONInsert Into omdLog(id,LogName,LogDate,UserName,RolesName,IP,Description,logType,storeID,repairListID,ItemID,storeCode,itemStatus,itemPlanDate,itemPlanTime,facilityType,logAction,belongFileID,moduleID) Select * FromOPENQUERY(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 omdAttachmentSet IDENTITY_INSERT omdAttachment ONInsert Into omdAttachment(attachID,belongType,storeID,ItemID,description,[fileName],filePath,fileReName,uploadDate,uploader,orderNo,uploadUserName,storeCode,fromType,fileActionType,belongFileID,moduleID,uploaderRoleID,fileType) Select * FromOPENQUERY(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