Simplest way to move records from one db to another db

Simplest way to move records from one db to another db

• cjpa15

  Hi,

  That idea is good. But i want to do the thing in my application.

  My application needs a database updation, to sync my SQL db with the actual live Database and report for changes in the table structure and other changes over a period of time.

  Hope i am clear..

  Regards,

  satish.r

  
  

• EVH

  You could create a SQL Server DTS package that pulls the information from Oracle and puts it into SQL Server. There are a lot of options this way including transformations and/or scheduling. DTS is fairly easy to use, there is a import wizard you can use to help you.

  In Enterprise Manager select the SQL Server instance where the data will be going. Select Tools->Wizards->Data Transformation Services->DTS Import Wizard and then follow the steps. Very easy bud.

  
  
  

• groves

  Yeah your clear. It sounds like you want a fine level of control of the overall process. Read in the data and structure from Oracle, do some checks on changes, report those changes, and then send the data to SQL Server.

  You want to load the current data from Oracle, then the previous update from SQL Server, compare the two and report. Check and see if the DataSet object has a Compare method.

  There are a couple of ways you could approach this. The best approach depends really on the resources you have avaliable, the amount of data your moving, and the type of information thats stored.

  
  
  

• simran

  Ya thanks for your help. I ll use the approach you mentioned.

  Regards,

  satish.r

  
  

• BogdanZ

  Yip, I think this should be do-able. Use the CREATE TABLE SQL statement in your application to build the temp table in SQL Server from the table structure downloaded from Oracle.

  Loop over the DataTable columns and build up the CREATE TABLE statement so that its an exact match to the table structure, execute it against SQL Server to make the temp table. Then use that table.

  Once you have the table structure use the DataAdapter (or the TableAdapter) object to copy the information into it.

  1. Get data from Oracle into DataSet

  2. Execute SQL command CREATE DATABASE on SQL Server

  3. For each table in DataSet build a CREATE TABLE query that makes the temp table and execute the query against SQL Server

  4. Use DataAdapter/TableAdapter to copy data into new SQL Server Database.

  I hope that helps.

  
  
  

• beaker101

  Yeah got it right. Thanks for your advice. It sounds great.

  But if a go this way:

  After fetching the data from oracle and move that data to SQL server and then check/compare the two tables, i feel this to be easier then to have multiple checks and for loops in my application.

  So now i want to move the oracle db info to a sql temp table.

  Any simple solution to move Instead of having multiple inserts

  Regards,

  satish.r

  
  
• Simplest way to move records from one db to another db