Native Oracle SQL result into SQL Compact Edition

[jtreefrog]

I've got a table adapter (In Visual Studio 2005) 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,etc.

Anyway, I want to take the *results* of that Oracle query and put the full dataset into a SQL Server Compact Edition database - and encapsulate it all 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? I don't have direct access to Oracle except through Visual Studio and I don't have a full version of SQL Server.

________________________________________
Give a man a match, he'll be warm for a minute. But light him on fire, and he'll be warm for the rest of his life.

 

[ptheriault]

Are your Oracle and SQL Servers on the same network? Can you ping the Oracle server from the SQL Server?

If you can run sp_addlinkedserver to add your Oracle server. Then you can use OPENQUERY to insert the data in a table in sql.

Here is an example. I run this all the time between my Oracle and SQL Server.

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] MavResource.dbo.MONTHLY_EARNED(
ERP_ID 
,POLICY_ID
,YEAR_EARN 
,MONTH_EARN 
,STATE 
,ELEMENT_NAME 
,CLASS_CODE
,WRITTEN_PREMIUM
,EARNED_PREMIUM 
,WRITTEN_EXPOSURE 
,EARNED_EXPOSURE 
,EVAL_PERIOD
)
[COLOR=blue]select[/color]
	[COLOR=#FF00FF]convert[/color](bigint, ERP_ID)
	,[COLOR=#FF00FF]convert[/color](bigint, POLICY_ID)
	,[COLOR=#FF00FF]convert[/color](bigint, YEAR_EARN)
	,[COLOR=#FF00FF]convert[/color](bigint, MONTH_EARN)
	,STATE
   ,ELEMENT_NAME
  ,CLASS_CODE  
  ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]money[/color],WRITTEN_PREMIUM)
  ,[COLOR=#FF00FF]convert[/color]([COLOR=blue]money[/color],EARNED_PREMIUM )
  ,WRITTEN_EXPOSURE 
  ,EARNED_EXPOSURE 
  ,[COLOR=#FF00FF]convert[/color](bigint, EVAL_PERIOD)
[COLOR=blue]from[/color] OPENQUERY
(RSDEV,
[COLOR=red]'select
[/color]    [COLOR=#FF00FF]to_char[/color](ERP_ID) [COLOR=blue]as[/color] ERP_ID
	,[COLOR=#FF00FF]to_char[/color](POLICY_ID) [COLOR=blue]as[/color] policy_id
	,[COLOR=#FF00FF]to_char[/color](YEAR_EARN) [COLOR=blue]as[/color] year_earn
	,[COLOR=#FF00FF]to_char[/color](MONTH_EARN) [COLOR=blue]as[/color] month_earn
	,STATE
   ,ELEMENT_NAME
  ,CLASS_CODE  
  ,[COLOR=#FF00FF]to_char[/color](WRITTEN_PREMIUM) [COLOR=blue]as[/color] written_premium
  ,[COLOR=#FF00FF]to_char[/color](EARNED_PREMIUM ) [COLOR=blue]as[/color] earned_premium
  ,WRITTEN_EXPOSURE 
  ,EARNED_EXPOSURE 
  ,[COLOR=#FF00FF]to_char[/color](EVAL_PERIOD) [COLOR=blue]as[/color] eval_period
   [COLOR=blue]FROM[/color] MONTHLY_EARNED
   [COLOR=blue]WHERE[/color] EVAL_PERIOD = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](EVAL_PERIOD)
                         [COLOR=blue]FROM[/color] MONTHLY_EARNED)[COLOR=red]'
[/color]   )

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jtreefrog]

I don't have a full version of SQL Server - only SQL Express and SQL Compact Edition. I was hoping do have this work in the background within the Windows program (I'm currently writing it with Visual Studio 2005/C#)

________________________________________
Give a man a match, he'll be warm for a minute. But light him on fire, and he'll be warm for the rest of his life.

 

[ptheriault]

you don't need the full version of SQL to add a linkserver. And you can run that DML from C#.

- Paul

- If at first you don't succeed, find out if the loser gets anything.