Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

Jay Te

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by Jay Te » Mon, 12 Nov 2007 10:19:01

I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:

select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table

There's also a CASE statement in there with some other data transformations.

Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.

For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.

I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!

Can anyone help

thanks,

Jon


ErikE

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by ErikE » Wed, 14 Nov 2007 11:20:02

Use an OracleDataReader and create records in the SQL CE database for each row your read.

Ilya Tumano

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by Ilya Tumano » Thu, 15 Nov 2007 13:22:04

It might be faster (and definitely easier from coding side) to load all that data into DataSet using batch load and then insert it all at once. It would take up some memory but since it's a desktop it probably does not matter.




Jay Te

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by Jay Te » Fri, 16 Nov 2007 12:21:03

I was actually hoping to avoid that Sad One of the 3 tables has about 750,000 records.

I'll give it a shot and see how it goes.

Jay Te

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by Jay Te » Sat, 17 Nov 2007 14:23:05

It might be faster (and definitely easier from coding side) to load all that data into DataSet using batch load and then insert it all at once. It would take up some memory but since it's a desktop it probably does not matter.



That's where I'm getting lost in the code (I'm a web PHP developer; this windows stuff is killin' me)

How do I do a batch like you're describing I've tried to call stuff like:

tableadapetername.insert(othertableadapter.get) - but I'm just not 'getting it'

any pointers

Jay Te

Native Oracle SQL -> SQL Server 2005 CE .sdf file -> using Visual Studio 2005?

by Jay Te » Mon, 19 Nov 2007 15:24:06

Use an OracleDataReader and create records in the SQL CE database for each row your read.


It was slooooow - but.... each subsequent addition is fast enough that the user won't notice.