oracle delete远程表,Oracle OpenQuery 函数对远程表执行 UPDATE、 INSERT, 或 DELETET示例 (不积跬步,无以至千里)...

1、对远程表执行 UPDATE、 INSERT, 或 DELETET

update openquery(linked1, 'select ssn from testlinked where ssn=2')

set ssn=ssn + 1

insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)

delete openquery(linked1, 'select ssn from testlinked where ssn>100')

SELECT @MachineID=MachineID from openquery(EHRORACLE,'select * from ATDTIMECARDMACHINE') where code=@DeviceNum

INSERT INTO OPENQUERY(EHRORACLE,'SELECT PERSONID,DataID,TIMECARDDATE,TIMECARDTIME,RECEIVEDATE,MACHINEID,DEPARTMENTID,DATAFROM,BUSINESSUNITID FROM ATDTIMECARDDATA')

SELECT @PersonID,newid(),@TIMECARDDATE,@TIMECARDTIME,convert(nvarchar(10),getdate(),120),@MachineID,@DEPARTMENTID,'1','0'

UPDATE OPENQUERY (EHRORACLE, 'SELECT * FROM ATDTIMECARDDATA  ')

SET timecardtime='0'+left(Timecardtime,1)+':'+substring(timecardtime,charindex(':',timecardtime)+1,2)+':'+right(timecardtime,2)

WHERE charindex(':',left(Timecardtime,2))>0  and PERSONID=@PersonID and DEPARTMENTID=@DEPARTMENTID and cast(Left(TIMECARDDATE,10) as datetime)=cast(@TIMECARDDATE as datetime) and Left(TIMECARDTIME,5)=Left(@TIMECARDTIME,5) and MachineID=@MachineID

2、使用 OpenQuery 动态执行 begin tran SET QUOTED_IDENTIFIER OFF SET XACT_ABORT ON declare @cmd varchar(2500)  declare @cmd1 varchar(2500)  declare @var varchar(20)  set @var = 'White'  declare @var1 varchar(20)  set @var1 = 'White1'  declare @var2 varchar(20)  set @var2 = 'Johnson1' select @cmd = "Update openquery(linked1,'select au_lname, au_fname from pubs.dbo.authors where au_lname = ''" + @var + "''' ) set au_lname = '" + @var1 + "', au_fname = '" + @var2 + "'" exec ( @cmd ) commit tran