Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Copy data between databases (Read 1805 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Copy data between databases
18.09.09 at 19:17:59
Copy data between databases
To copy data between two databases you need to retrieve the source data using a query and then use the multiple rows insert function to insert the data into the target database.
Assuming the table is already present in the two databases, the process is as follows.
Selecting data from the source table in the source database. Connect using SQL*XL to your source database and use the SQL dialog to run the following query (replace with your query):
select * from mytable where code like 'FC%'  
In Excel you can make some alterations if required. I decided for example to remove some rows I don't need to copy and some id fields in foreign key columns needed changing.
Now, usually a table has a sequence number as primary key. These will be different between source and target databases. Simply omit the column when you insert (see below). You can remove the column from Excel so you do not include it by mistake in the insert step.
Disconnect SQL*XL from your source database and connect to the target database.  
Start SQL*XL's Insert Multiple Rows.
Enter the name of the table in the table field.
Put your cursor in the column names field and then select the cells on the sheet where the column names are displayed.
Put the cursor in the data field and then select the cells on the sheet where the data is displayed.
Press the OK button to start the insert proces.
The insert process copies all the data into the target table.  
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1