Insert data from another DB in tables

Question

Hi all, I'm having some issue here. Let me explain.

So I was about done with migration of this project and I've decided to run the test suite to make sure the logic was still working as expected. Unfortunately, it didn't... but that's not the issue.

At the end of the suite, there was a nice script that execute a delete on the datas of 5 tables of our developement database. That would be fine if there was also a script to actually populate the database...

The good side is that we still have plenty of data in production environement, so I'm looking for a way and/or possibly a tool to extract the data on these 5 particular tables in production and insert them in dev environement. There is all sort of primary and foreign key between these tables, maybe auto-increment fields, (and also A LOT of data) that's why I don't want to do it manually.

Our database is db2 v9 if it makes any difference. I'm also working with SQuirreL, there might be a plugin, but I haven't found yet.

Thanks

Answer 1

This is sort of a shot in the dark, as I've never used db2, but from previous experience, my intuition immidiately says "Try csv". I'm willing to bet my grandmother you can import / export csv-files in your software ( why did i just start thinking of George from Seinfeld? ) This should also leave you with FKs and IDs intact. You might have to reset your auto increment value to whatever is appropriate, if need be. That, of course, would be done after the import

In addittion, csv files are plaintext and very easily manipulated should any quirks show their head.

Best of luck to you!

Answer 2

Building on Arve's answer, DB2 has a built-in command for importing CSV files:

IMPORT FROM 'my_csv_file.csv'
OF del
INSERT INTO my_table

You can specify a list of columns if they are not in the default order:

IMPORT FROM 'my_csv_file.csv'
OF del
-- 1st, 2nd, 3rd column in CSV
METHOD P(1, 2, 3)
INSERT INTO my_table
(foo_col, bar_col, baz_col)

And you can also specify a different delimiter if it's not comma-delimited. For example, the following specifies a file delimited by |:

IMPORT FROM 'my_csv_file.csv'
OF del
MODIFIED BY COLDEL|
-- 1st, 2nd, 3rd column in CSV
METHOD P(1, 2, 3)
INSERT INTO my_table
(foo_col, bar_col, baz_col)

There are a lot more options. The official documentation is a bit hairy:

• DB2 Info Center | IMPORT command

Answer 3

Do you have access to the emulator? there's a function in the emulator that allows you to import CSV into tables directly.