Moving data from one database to other

Shell Programming and Scripting
1

Dear All,

I have 2 databases, There is a lot of data in both the databases, i would like to move some data from one database to the other. I would like to accept 2 parameters from the user, i.e. emplyee id & dept, on entering the 2 i will unload all the data from the tables to the flat files. Now i need to load this data from the flat files in to the other database. If the data exists in the new database it should update the data. Any workarounds on it. Thanks in advance.

Regards,

lloyd

2

It would be helpful to understand what database you are working with (Oracle, DB2, Access, etc) as well as which Operating System.

3

Also, the OP has posted this same thread in two forums.

Read the rules, lloydnwo, and ye shall reap the benefits.

4

Hi,

Thanks for the help, my database is informix and my os is sun solaris 8, any help is much appreciated.

Regards,

lloyd

5

If the employee_id/department are a unique reference then, you can do something like this in a script:

EMP_ID=123
DEPT=SALES

dbaccess source_db - << !
unload to source.unl select * from source_table
where employee_id = $EMP_ID
and department = $DEPT;
!

Insert the results to a temporary table with:

dbaccess destination_db - << !
load from source.unl insert into tt_source;
!

Then:

dbaccess destination_db - << !
update dest_table set
dest_table.col1 =
(select tt_source.col1
from tt_source
where tt_source.employee_id = dest_table.employee_id
and tt_source.department = dest_table.department);
where dest_table.employee_id = $EMP_ID
and dest_table.department = $DEPT;
!

Take a backup of your databases before you try this! Use dbexport to do this.

I don't think this will be too fast if you have many updates to perform. Consider doing this with an esql/c application if you have esql/c available.

6

Hi mbb,

Thanks for the feedback, but i need to accept the values from the user and pass it on to the other script.

Regards,

lloyd

7

To accept user input in sh or ksh then change:

EMP_ID=123
DEPT=SALES

for:

echo "enter employee id: \c"
read EMP_ID
echo "enter department: \c"
read DEPT

The read command is basic scripting. If you code all the statements I have given you in the previous post in the same script, the values read into EMP_ID and DEPT will be substituted where they appear in the sql statement.