Copy Oracle table to SQL Server

[coder_t2]

Hey guys, would would be the best way to copy an entire table from Oracle to SQL Server? An openquery seems inefficient, so I am thinking something with SSIS. I am still new to SSIS and am still learning. At first I thought doing a DataFlow task and just using a SQL query that selects the 6 fields that I need from the table would be good. But then I figured there may be a way to bulk copy the entire table. Basically I want a table on my SQL Server database to be identical to this Oracle table. The oracle table does change on a daily basis. New records are added, and others are modified.

 

[KLin]

Why does the openquery function seem inefficient? Do the SQL and Oracle servers reside on the same LAN, or is there a WAN in between them?

INSERT into tblSQLServerTable (1, 2, 3, 4, 5, 6)
SELECT 
    1, 2, 3, 4, 5, 6
FROM 
    OPENQUERY(OracleLinkedServer, 'SELECT 1, 2, 3, 4, 5, 6 FROM tblOracleServerTable') O LEFT JOIN tblSQLServerTable S On O.UniqueFieldID = S.UniqueFieldID
WHERE 
    S.UniqueFieldID Is Null

 

[coder_t2]

There is a WAN between servers. They are located in different states. Why do you have a join in your code. Wouldn't it just be easier to do this.

Select * into SqlServTable
from openquery(linkedserver, select * from oracletable)

And I am using SQL Server 2005 btw.

 

[JACKDRUID]

sounds like a replication topic

http://msdn.microsoft.com/en-us/library/cc966428.aspx

 

[KLin]

sounds like a replication topic

http://msdn.microsoft.com/en-us/library/cc966428.aspx

I would definitely look into this.

 

[KLin]

There is a WAN between servers. They are located in different states. Why do you have a join in your code. Wouldn't it just be easier to do this.

Select * into SqlServTable
from openquery(linkedserver, select * from oracletable)

And I am using SQL Server 2005 btw.

So you don't have to keep reloading the whole table over and over and over and over, etc. Take a look at the link that JACKDRUID posted.

 

[coder_t2]

Ok. I really like the looks of this, except does it maintain indexes? And could I create an extra artificial column? Such as creating a column that is (col1 + col2)?

 

[KLin]

Don't create calculated columns. Just create a view of the table, and include an expression field that calculates the values of the 2 columns.

 

[JACKDRUID]

Ok. I really like the looks of this, except does it maintain indexes?

no. you need to rebuild/maintain your indexes on the replicated tables. in sql server there is a scheduler / monitor you can setup to do it automatically.

replication is on per table basis, which is good so you can do your own stuff and try replicate as little as possible.

 

[coder_t2]

Don't create calculated columns. Just create a view of the table, and include an expression field that calculates the values of the 2 columns.

Hmm okay. I wouldn't actually be doing a math operation, it would be conctatenation. So First Name + Last Name kind of deal. I use it as a key for joining other tables, and I find it is much more efficient to have the column already concatenated than concatenating it during the join.

 

[JACKDRUID]

Hmm okay. I wouldn't actually be doing a math operation, it would be conctatenation. So First Name + Last Name kind of deal. I use it as a key for joining other tables, and I find it is much more efficient to have the column already concatenated than concatenating it during the join.

what about ppl with same firstname + lastname?...

you NEED to use a primary key / ID that is non-duplicated, even better would be an uniqueidentifier type.. for membership information, both ID and uniqueidentifier are used to ensure some kind of "sequential ordering" at the same time guarenteed "Uniqueness"

but at very least , you need to have a ID column in any table.

 

[KLin]

what about ppl with same firstname + lastname?...

you NEED to use a primary key / ID that is non-duplicated, even better would be an uniqueidentifier type.. for membership information, both ID and uniqueidentifier are used to ensure some kind of "sequential ordering" at the same time guarenteed "Uniqueness"

but at very least , you need to have a ID column in any table.

Amen. Concatenating 2 columns and using that as a unique ID on a table is just asking for trouble.

 

[coder_t2]

Ok ok. Bad example. I don't actually use firstname + lastname as an ID cause lots of people have the same names. I was just giving an example that I concactenate 2 columns to create a unique ID. Now, I know you guys think it's a bad idea, but it's not 2 random columns. They are columns that are actually a unique ID. So we use a pharmacy ID and patient number to create a unique patient ID. Since the same patient numbers can be used at multiple pharmacies, but no two patient numbers are the same at the same pharmacy. Get it? The two columns just aren't concatenated together for their own field in the datawarehouse. So I have to do it on my own.