×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

Duno your purpose. But just give you the result from my platform. (2 SQL server in the network)

3. you should modify Diff to DateDiff
Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From [LinkedServer].[testdb].[dbo].[VTABLE]
Where DateDiff(Day,EffectiveDate,getDate())<1

sELECT * FROM @temp

-- VTABLE records:
Code EffectiveDate Rate DDescription
cd1 2009-01-01 00:00:00.000 100 sql linked to sql
cd2 2009-01-02 00:00:00.000 200 sql linked to sql
cd3 2009-01-03 00:00:00.000 300 sql linked to sql
cd4 2009-01-21 00:00:00.000 400 sql linked to sql

-- Result:
cd4 2009-01-21 00:00:00.000 400.000000000
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 紧急求助,数据库问题,谢谢!
    本文发表在 rolia.net 枫下论坛因对ORACLE不熟而且没法测试,请用ORACLE和SQL SERVER的朋友们帮个忙,测一下下面的CASE:

    目的: 在ORACLE LINKED SERVER上查询一个表或VIEW,把当天数据捣到sql server的表中

    1. ORACAL上有个数据库XXX包含这样的VTABLE:
    Code Varchar2(3)
    EffectiveDate Datetime
    Rate Numeber(12,9)
    DDescription Varchar2(100)
    输几条记录做测试用 ,请用不同日期,其中至少一个用当前日期

    2. 在SQL SERVER 2005 上建立ORACLE LINKED SERVER,名称就叫ORALINKEDSrv好了

    3. 在 SQL SERVER 2005上做下面的QUERY:

    Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

    Insert Into @temp(Code,EffectiveDate,Rate)
    Select Code,EffectiveDate,Rate
    From ORALINKEDSrv.XXX.SYSOWNER.VTABLE -- SYSOWNER请改用实际OWNER名
    Where Diff(Day,EffectiveDate,getDate())<1

    sELECT * FROM @temp

    Go


    4.假如3不行,改用OPENQUERY -- 对其中ORACLE的QUERY可能有错,请修改

    Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

    Insert Into @temp(Code,EffectiveDate,Rate)
    Select Code,EffectiveDate,Rate
    From OpenQuery(ORALINKEDSrv,'Select Code,trunc(EffectiveDate)=date(),Rate From XXX.SYSOWNER.VTABLE')


    SELECT * FROM @temp

    Go

    请把3和4的结果贴在这里或者请PM,多谢!更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • up
    • 改正
      4.假如3不行,改用OPENQUERY -- 对其中ORACLE的QUERY可能有错,请修改

      Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))

      Insert Into @temp(Code,EffectiveDate,Rate)
      Select Code,EffectiveDate,Rate
      From OpenQuery(ORALINKEDSrv,'Select Code,EffectiveDate,Rate From XXX.SYSOWNER.VTABLE where trunc(EffectiveDate)=[DATE]')


      SELECT * FROM @temp

      Go
    • How about SQL Server Replication? I used the service to replicate data from Oracle to SQL Server a couple of years ago and it worked very well.
    • Duno your purpose. But just give you the result from my platform. (2 SQL server in the network)
      3. you should modify Diff to DateDiff
      Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
      Insert Into @temp(Code,EffectiveDate,Rate)
      Select Code,EffectiveDate,Rate
      From [LinkedServer].[testdb].[dbo].[VTABLE]
      Where DateDiff(Day,EffectiveDate,getDate())<1

      sELECT * FROM @temp

      -- VTABLE records:
      Code EffectiveDate Rate DDescription
      cd1 2009-01-01 00:00:00.000 100 sql linked to sql
      cd2 2009-01-02 00:00:00.000 200 sql linked to sql
      cd3 2009-01-03 00:00:00.000 300 sql linked to sql
      cd4 2009-01-21 00:00:00.000 400 sql linked to sql

      -- Result:
      cd4 2009-01-21 00:00:00.000 400.000000000
    • 1. Date filter condition. In Oracle, it should be EffectiveDate >=TRUNC(Sysdate -1) or likewise. 2. Not sure what’s your goal, query (or join query) data in Oracle database from sql server, or transfer data from Oracle?