update from Oracle -> SQL Server 2000

Question

Post reply

update from Oracle -> SQL Server 2000

Seth Washeck

Mr or Mrs. 500

Points: 578
More actions
July 26, 2004 at 4:18 pm

#360511

We have started a SQL Server instance that gets data from a (remote) Oracle Warehouse. I have used DTS to get the initial set of data that we need but need a method to update the SQL Server from Oracle. This is strictly a table - table tranformation.
A few suggestions so far:
(1) Truncate the SQL Server table and just re-acquire the data.
(2) Set up Oracle as linked server and update from join query ...
I like this one but I can't figure out how to get around the issue of having too many arguments [linked server].[schema].[table name].[element] = BARF!
(3) Create a temp table do a binary checksum ... update ... dump the temp table
Kindest Regards,
Seth Washeck
If nothing in this world satisfies me, perhaps it is because I was made for another world.
C. S. Lewis

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dbuchan SSCrazy Points: 2328 More actions · Answer 1

1. Simple but not verra elegant

2. Create linked server views using Openquery and join on the results. Much less BARFY

3. Suppose it would work.

The systems fine with no users loggged in. Can we keep it that way ?br>

Seth Washeck Mr or Mrs. 500 Points: 578 More actions · Answer 2

Thanks! I had just "discovered" Openquery.

Kindest Regards,

Seth Washeck

If nothing in this world satisfies me, perhaps it is because I was made for another world.
C. S. Lewis

ghughes SSCrazy Points: 2201 More actions · Answer 3

ok, i'll bite.....what is openquery? where do I find this, and what does it do?

dbuchan SSCrazy Points: 2328 More actions · Answer 4

Ala Books On Line:

OPENQUERY

Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax

OPENQUERY ( linked_server , 'query' )

and an example (Very messy query built by Business Objects (urk)):

SELECT * FROM OPENQUERY(BOCHRIS, 'SELECT

EMDET.DET_SUR_INITA Surname_Initial,

EMDET.DET_NUMBERA Employee_Number,

EMPOS.POS_TITLEA Position_Title,

EMPOS.POS_NUMBERA Position_Number,

EmployeeOrganisationL1.GNA_ORG_CODEA || '' - '' || EmployeeOrganisationL1.GNA_ORG_NAMEA L1_Desc,

EmployeeDivisionL2.GNA_ORG_CODEA || '' - '' || EmployeeDivisionL2.GNA_ORG_NAMEA L2_Desc,

EmployeeBranchL3.GNA_ORG_CODEA || '' - '' || EmployeeBranchL3.GNA_ORG_NAMEA L3_Desc,

decode(EMPOS.POS_STATUSA,''FT'',''Full Time Temporary'',

decode(EMPOS.POS_STATUSA,''FP'',''Full Time Permanent'',Decode(EMPOS.POS_STATUSA,''EL'',''Elected Member'',Decode(EMPOS.POS_STATUSA,''PT'',''Part Time Temporary'',Decode(EMPOS.POS_STATUSA,''PP'',''Part Time Permanent'',Decode(EMPOS.POS_STATUSA,''VC'',''Variable Casual'',EMPOS.POS_STATUSA)))))) Type,

EMPOS.POS_EMP_OCCA,

EmployeeUnitL4.GNA_ORG_CODEA || '' - '' || EmployeeUnitL4.GNA_ORG_NAMEA L4

FROM

EMDET,

EMPOS,

ORGNA EmployeeOrganisationL1,

ORGNA EmployeeDivisionL2,

ORGNA EmployeeBranchL3,

ORGNA EmployeeUnitL4

WHERE

( EmployeeOrganisationL1.GNA_LEVEL_NOA=1 AND EmployeeOrganisationL1.GNA_EXP_DATED IS NULL )

AND ( EmployeeDivisionL2.GNA_LEVEL_NOA=2 AND EmployeeDivisionL2.GNA_EXP_DATED IS NULL )

AND ( EmployeeBranchL3.GNA_LEVEL_NOA=3 AND EmployeeBranchL3.GNA_EXP_DATED IS NULL )

AND ( EMPOS.POS_L1_CDA=EmployeeOrganisationL1.GNA_ORG_CODEA )

AND ( EMPOS.POS_L2_CDA=EmployeeDivisionL2.GNA_ORG_CODEA )

AND ( EMPOS.POS_L3_CDA=EmployeeBranchL3.GNA_ORG_CODEA )

AND ( EmployeeUnitL4.GNA_LEVEL_NOA=4 AND EmployeeUnitL4.GNA_EXP_DATED IS NULL )

AND ( EMPOS.POS_L4_CDA=EmployeeUnitL4.GNA_ORG_CODEA )

AND ( EMPOS.DET_NUMBERA=EMDET.DET_NUMBERA )

AND (

( (EMPOS.POS_ENDD is null or EMPOS.POS_ENDD >= sysdate ) )

)

GROUP BY

EMDET.DET_SUR_INITA,

EMDET.DET_NUMBERA,

EMPOS.POS_TITLEA,

EMPOS.POS_NUMBERA,

EmployeeOrganisationL1.GNA_ORG_CODEA || '' - '' || EmployeeOrganisationL1.GNA_ORG_NAMEA,

EmployeeDivisionL2.GNA_ORG_CODEA || '' - '' || EmployeeDivisionL2.GNA_ORG_NAMEA,

EmployeeBranchL3.GNA_ORG_CODEA || '' - '' || EmployeeBranchL3.GNA_ORG_NAMEA,

decode(EMPOS.POS_STATUSA,''FT'',''Full Time Temporary'',

decode(EMPOS.POS_STATUSA,''FP'',''Full Time Permanent'',

Decode(EMPOS.POS_STATUSA,''EL'',''Elected Member'',Decode(EMPOS.POS_STATUSA,''PT'',''Part Time Temporary'',Decode(EMPOS.POS_STATUSA,''PP'',''Part Time Permanent'',Decode(EMPOS.POS_STATUSA,''VC'',''Variable Casual'',EMPOS.POS_STATUSA)))))),

EMPOS.POS_EMP_OCCA,

EmployeeUnitL4.GNA_ORG_CODEA || '' - '' || EmployeeUnitL4.GNA_ORG_NAMEA

')

The systems fine with no users loggged in. Can we keep it that way ?br>