Need to add data from a column in one table to column comments in another

Question

I'm very new to SQL developer and am now working with an existing Database. The tables do not have column comments and I've been tasked to add them. I created an excel spreadsheet of the column descriptions and imported as a table. I need to write a query for the table with the comments in a column (lets call it Table_1, comments) and insert those comments into the Column Comments for Table_2. If it helps there is a unique column_id number for each comment so comment for Table_1 should be inserted at comment row on Table_2 where Table_1.column_ID = Table_2.column_ID. Thanks in adavance!

I have tried selecting the column from Table_1 and inserting as Comment on Column in Table_2 and got an error. Also tried reversing the order of these as well. I was able to insert each one individually using: -Comment on Column table_name.column_name is 'this comment'; But it is tedious and I want to enter all of the comments at once using a query.

Answer 1

This is about adding a comment to table's column in data dictionary. To do that, you'll need dynamic SQL. Here's an example.

SQL> set serveroutput on

This is table that contains comments you'd want to apply:

SQL> select * from comments;

TABLE_NAME COLUMN_NAME  COMMENTS
---------- ------------ ------------------------------
DEPT       DEPTNO       Department ID
DEPT       DNAME        Department name
EMP        SAL          Salary (USD)

For example, DEPT table currently doesn't have any comments:

SQL> select table_name, column_name, comments
  2  from user_col_comments
  3  where table_name = 'DEPT';

TABLE_NAME COLUMN_NAME  COMMENTS
---------- ------------ ------------------------------
DEPT       DEPTNO
DEPT       DNAME
DEPT       LOC

Procedure displays command which is then being executed:

SQL> declare
  2    l_str varchar2(2000);
  3  begin
  4    for cur_r in (select table_name, column_name, comments
  5                  from comments
  6                 )
  7    loop
  8      l_str := 'comment on column ' || cur_r.table_name ||'.'||
  9        cur_r.column_name || ' is ' || chr(39) || cur_r.comments || chr(39);
 10      dbms_output.put_line(l_str);
 11      execute immediate l_str;
 12    end loop;
 13  end;
 14  /
comment on column DEPT.DEPTNO is 'Department ID'
comment on column DEPT.DNAME is 'Department name'
comment on column EMP.SAL is 'Salary (USD)'

PL/SQL procedure successfully completed.

Result:

SQL> select table_name, column_name, comments
  2  from user_col_comments
  3  where table_name = 'DEPT';

TABLE_NAME COLUMN_NAME  COMMENTS
---------- ------------ ------------------------------
DEPT       DEPTNO       Department ID
DEPT       DNAME        Department name
DEPT       LOC

SQL>