Several ways for DB2 to write data to a table

You can add data to a table as follows:
1. insert into table values (...)
Write data according to the specified column in the table. If only the table name is given, the data is added in the column order.
Insert into table (col1, col2) values (value1, value2)
Insert into table (col1, col2) values (value1, value2), (value3, value4 ),...
 
2. insert into table select...
This statement indicates that the added data comes from one or more tables or attempts to query. The query can be sorted and grouped.
For example,
Db2 => create table empk (empno char (6), ename varchar (15), salary decimal (9, 2 ))
The DB20000I SQL command is successfully completed.
The three employees with the highest salaries:
Db2 => insert into empk select empno, lastname, salary from employee order by salary desc fetch first 3 rows only
The DB20000I SQL command is successfully completed.
Db2 => select * from empk
EMPNO ENAME SALARY
--------------------------------
000010 HAAS 152750.00
000030 KWAN 98250.00
000070 PULASKI 96170.00
Three records are selected.
 
3. Copy table structure and data
We can also copy the table structure when creating a table, and then use this method to add data.
Create table emp_test like employee
Insert into emp_test select * from employee where salary> 80000.00
 
You can also copy data while copying the table structure.
Create table emp_test as (select * from employee where salary> 80000.00)
Data initially deferred refresh immediate | deferred
After creating a table, refresh it:
Db2 => refresh table emp_test
The DB20000I SQL command is successfully completed.
Otherwise, an error occurs: SQL0668N cannot perform operations on the table "LENOVO. EMP_TEST". The Reason code is "1 ". SQLSTATE = 57016
 
If you only want to copy the table structure and do not need to copy data, you can create a table as follows:
Create table emp_tt as (select empno, job, salary from employee) definition only --★
Or
Create table emp_tt as (select empno, job, salary from employee) with no data --★
Db2 => describe table emp_tt
Data Type Column
Column name mode data type name long decimal place NULL
--------------------------------------------------------------------------------
Empno sysibm character 6 0 No
Job sysibm character 8 0 is
Salary sysibm decimal 9 2 is
Three records are selected.
 
Db2 => select count (*) from emp_tt
1
-----------
0
1 record selected.
 
4. Merge into statement
The merge into statement is used to merge data from one table to another. When the data already exists, we do not need to process or update it;
For details, see the usage of the DB2 Merge Into statement.

-- The end --