Home » SQL & PL/SQL » SQL & PL/SQL » Insert result of SELECT * into table without knowing column names (merged)
Insert result of SELECT * into table without knowing column names (merged) [message #220940] Thu, 22 February 2007 16:17 Go to next message
d000hg
Messages: 5
Registered: February 2007
Junior Member
I want to read a very large recordset from a table, and write data to another database. This is being by C# applciation making queries against Oracle 10 DBs on both sides.

There is a requirement to batch the operation - retreive only N records at a time from the source database. Can someone tell me how I might do this, or what topics I should be searching for?

Thankyou.

[Updated on: Thu, 22 February 2007 16:18]

Report message to a moderator

Insert result of SELECT * into a table without knowing column names [message #220941 is a reply to message #220940] Thu, 22 February 2007 16:22 Go to previous messageGo to next message
d000hg
Messages: 5
Registered: February 2007
Junior Member
I have two Oracle 10 DBs. They have tables with identical structures. I have to read records from a table in one DB, using a C# app, and write these records into a table on the other DB.

Because the table has lots columns I don't want to type them all out in the insert statement. Also it is better for the future if I don't hard-code the columns, in case a column is added or removed. So how can I do this? Should I somehow retrieve a list of all column names and programmatically build a huge string lsiting them all? Or is there some way to have my result/record set in C# from the SELECT and sort of do INSERT * into the target table?

Thankyou.
Re: Insert result of SELECT * into a table without knowing column names [message #220946 is a reply to message #220941] Thu, 22 February 2007 18:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
insert into t2 (select * from t1@otherDB);

otherDB is a Database link in oracle, that will talk to other Database.
To create a database link
search this forum/google/docs.
Here is a handy refernce
http://www.psoug.org/reference/db_link.html
Re: Batching [message #220947 is a reply to message #220940] Thu, 22 February 2007 18:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> retreive only N records at a time
Why?
In most cases, doing everything in a single transaction is better.
Re: Batching [message #220948 is a reply to message #220947] Thu, 22 February 2007 18:27 Go to previous messageGo to next message
d000hg
Messages: 5
Registered: February 2007
Junior Member
Because it's in the requirements specification! I guess they don't want C# returned several megabytes of data. Perhaps I can argue this point if you can link to a supporting article?
Re: Batching [message #220950 is a reply to message #220948] Thu, 22 February 2007 19:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It would be faster NOT to use C#. You could just insert over a DB Link, or use DataPump in 10g.

If you are determined to use C#, you should be reading up on ARRAY PROCESSING. This means that you can Fetch n rows from the database in one hit and write n rows in one hit. Pro*C does this nicely. Maybe there is a C# version of Pro*C - I'm not sure. Otherwise you might have to use OCI. I believe there is a direct-path load interface for OCI.

Ross Leishman
Re: Batching [message #221003 is a reply to message #220950] Fri, 23 February 2007 02:48 Go to previous messageGo to next message
d000hg
Messages: 5
Registered: February 2007
Junior Member
I'm confused Sad
All my search for that kind of stuff didn't find much of use.
Re: Batching [message #221010 is a reply to message #220940] Fri, 23 February 2007 03:48 Go to previous messageGo to next message
d000hg
Messages: 5
Registered: February 2007
Junior Member
OK I might have got permission to do it your way - using DB link.
Are there any issues if the number of rows gets big, or is this still the best way to do a table-table copy across different databases?
Re: Batching [message #221044 is a reply to message #221010] Fri, 23 February 2007 07:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just the usuall issues.

For example :

1. If its a big amount of data and the network connection is slow it might slow down all the other users using the DBs.

2. If its a REALLY big amout of data you might run out of undo space on the receiving database if you do it in one transaction.

3. Because of 1. you might only have a limited time window each day in which to run the transfer, so it has to be broken down into several chunks.

I could imagine the requirement "do n rows at a time" could be because of that, but those would also have been issues if you used C#, and can also be solved with a DB link.

The easiest way would be to break it into chunks based on the data. e.g. transfer transactional data in day-chunks, transfer oter data based on ranges on some id column, etc.
Re: Batching [message #221074 is a reply to message #221044] Fri, 23 February 2007 12:21 Go to previous messageGo to next message
nvs_krishna
Messages: 3
Registered: February 2007
Location: Atlanta
Junior Member

adding to the above options..

If teh table data is huge insert into table 1 (select * from table2@DB2 ) takes lot of time and puts the systems down.

1) If you just want to copy the data, one of the ways is the FTP the flat file and use SQL*loader to insert. In this way you can schedule some Wundows or UNIX jobs to do this in the night.

2) Seperate the data and insert set by set. Exp, Let say I have a data in emp table with 1 million records. I use the beloe method in loop insted of a single query.
use dynamic queries to build this.

insert into emp1@db1 (select * from emp2@db2 where emp id between 1 and 1000)

insert into emp1@db1 (select * from emp2@db2 where emp id between 1001 and 2000)

Hope this will help you.

[Updated on: Fri, 23 February 2007 12:22]

Report message to a moderator

Re: Batching [message #225108 is a reply to message #221074] Sat, 17 March 2007 12:21 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you plan to insert million rows then i wouldn't use the dblink i will export from the source database, ftp the file and import it my local database.
Previous Topic: Please help me in using pivot and concatenate together.
Next Topic: Removing the first occurance of a number in a string
Goto Forum:
  


Current Time: Sat Dec 10 20:17:06 CST 2016

Total time taken to generate the page: 0.08420 seconds