Oracle如何实现跨数据库查询
https://blog.csdn.net/Jerry_an/article/details/87873923
create public database link "dbtrue" connect to "ztzs_erp2" identified by "密码" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.13*)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = "oradb") ) )'; select * from "Project"@dbtrue.ztzs; insert into "Project"( "Id","EnginNo","No","Name","Status","BuildArea","BuildCost","DeptNo","Comment","CreateUser","CreateTime","UpdateUser","UpdateTime","SubStatus", "DeptName","Members","ProjectType","TaxMode","ProjectStartDate","ProjectEndDate","ProjectSetDate","ProjectEnterDate","ProjectBuildFinishDate", "ProjectAcceptanceDate","ProjectDeliverDate","ProjectWarrantyEndDate","ProjectCloseDate","ProjectStopDate","ProjectChangeDate","AmountExcludeTax", "ProjectBidNo","LaborTax","AccountingVoucherNo","SingleCost","EstateCompanyNo","ModelRoomCompletionTime","Houses","PublicArea","PredictBeginTime", "PredictEndTime","IsFirstCheckPublicArea","LaborCompanyNo","LaborCompanyName","IsSettled" ) select "Id","EnginNo","No","Name","Status","BuildArea","BuildCost","DeptNo","Comment","CreateUser","CreateTime","UpdateUser","UpdateTime","SubStatus", "DeptName","Members","ProjectType","TaxMode","ProjectStartDate","ProjectEndDate","ProjectSetDate","ProjectEnterDate","ProjectBuildFinishDate", "ProjectAcceptanceDate","ProjectDeliverDate","ProjectWarrantyEndDate","ProjectCloseDate","ProjectStopDate","ProjectChangeDate","AmountExcludeTax", "ProjectBidNo","LaborTax","AccountingVoucherNo","SingleCost","EstateCompanyNo","ModelRoomCompletionTime","Houses","PublicArea","PredictBeginTime", "PredictEndTime","IsFirstCheckPublicArea","LaborCompanyNo","LaborCompanyName","IsSettled" from "Project"@dbtrue.ztzs a where a."Id" not in (SELECT "Id" from "Project") AND a."No" NOT IN (SELECT "No" FROM "Project") AND a."UpdateTime" IS NOT NULL;
MSSql 实现跨库查询:
EXEC sp_addlinkedserver @server = N'oldErpTrue', @srvproduct=N'ZSErp',@provider=N'SQLOLEDB', @datasrc=N'172.16.10.100' EXEC sp_addlinkedsrvlogin @rmtsrvname = N'oldErpTrue', @locallogin = NULL ,@useself = N'False', @rmtuser = N'sqluser', @rmtpassword = N'qwe_123456*' select * from oldErpTrue.ZSErp.Material.Material;
insert into Material.MaterialSheet
select * from oldErpTrue.ZSErp.Material.MaterialSheet a
where a.Id not in (SELECT Id from Material.MaterialSheet) and a.SheetNo not in (SELECT SheetNo from Material.MaterialSheet);