TASKS, IMPORT DATA - select fields only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

TASKS, IMPORT DATA - select fields only

Author

Topic

pwvailla
Starting Member

31 Posts

Posted - 2010-12-02 : 15:49:24

Hi,

Forced to use SQL Server 2005, then the SSIS product... I need help!

I am trying to import data from Oracle ERP into SQL Server 2005. I have the linked server mapped fine, I can do a full table import (all columns) and it chunks away forever.

What I want to do is:

1. Import only select columns
SELECT PO, PO_LINE_NO, PO_DESC ...

2. Import only select rows provided the key value exists in a reference table in another server & databse table:

SELECT PO, PO_LINE_NO, PO_DESC
FROM PO.PO_LINES_ALL (i.e. from the Oracle ERP database)
WHERE PO, PO_LINE_NO EXISTS
(SELECT * FROM dbo.valid_po) i.e. a reference table on SQL Server database

Can I do #1 using the IMPORT DATA feature in SQL Server Management Studio Std Ed?

Can I do #2? This one is important since I don't have dba right on the Oracle ERP system and I have over a million rows in the table and I need a way to extract only the rows I need or I will never get a complete results set back (due to network, database and server restrictions/performance).

Any thoughts?

Thanks...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 15:56:58

How many rows do you need?
Easy options
populate a table in oracle with the pks of rows needed and join to that for the extract
Get the pk and cols needed into a staging table on sql server and join to that to get the rows needed
Do a call to oracle for each pk to get single rows.
Export pks and cols needed from oracle to a file, zip the file, copy to sql server, unzip, load to staging table join to get rows needed.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pwvailla
Starting Member

31 Posts

Posted - 2010-12-02 : 16:11:51

Help me understand how you do option #2. I don't have any ability to create table in Oracle instance or I would be a happy man. I have a reference table in SQL Server, but how do I use that in the IMPORT DATA tool?

quote:
Originally posted by nigelrivett

How many rows do you need?
Easy options
populate a table in oracle with the pks of rows needed and join to that for the extract
Get the pk and cols needed into a staging table on sql server and join to that to get the rows needed
Do a call to oracle for each pk to get single rows.
Export pks and cols needed from oracle to a file, zip the file, copy to sql server, unzip, load to staging table join to get rows needed.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources