DB2 Export data into INSERT Statements

Let’s say we have a table called EMPLOYINFO, and you’re asked to export its content into INSERT statements, how would you go about it?

Task: Copy the contents of EMPLOYINFO to EMPLOYINFO_NEW table, but ensure the data is exported from EMPLOYINFO in the format of INSERT queries.

Table EMPOYINFO:

db2export1

The final result should be like so:

SOLUTION:

One command can allow us to EXPORT data in the INSERT queries format.

Let’s understand this command a bit better:

db2 -xq -> Suggests that nothing else would be outputted besides the result of the command. It just ensures the result is cleaner without extra stuff in there.

INSERT INTO EMPLOYINFO_NEW (EMPID,FIRS_TNAME,LAST_NAME,AGE) -> This is a generic way of writing out INSERT queries, where we define the field names.

DEFAULT -> We’re not specifying the name of the field in here, unlike the other fields, because data structure of that table is defined as GENERATED ALWAYS which populated that particular field values incrementally. We could have written the command like so,

but then when it comes inserting those records into EMPLOYINFO_NEW, it won’t allow to insert them, because the field is defined as AUTO GENERATED. As a result, we set that field as DEFAULT in the command.

”’||FIRST_NAME||”’  -> This syntax suggests that you’re outputting a value in the STRING format.

‘||AGE||  -> This syntax however suggests you’re outputting a value in the INTEGER format.

 

Leave a Reply