|
Dear Experts, I need to insert data in a table say 'A' from another table say 'B' wherein the column values that need to be entered in table are in 2 columns in table B such that column name is in 1 column and column value is in another. How do I achieve such insertion? |
-- Let's suppose we have 2 tables A and B. -- Table A has 2 columns X and Y. -- Table B has 2 columns D and F. CREATE TABLE A ( X INTEGER, Y INTEGER ); CREATE TABLE B ( D VARCHAR ( 10 ), F INTEGER ) -- There are 2 rows in Table B. -- Column D of table B has values X and Y i.e. the column name of table A. -- Column F of table B has values 10 and 20. INSERT B VALUES ( 'X', 10 ); INSERT B VALUES ( 'Y', 20 ); COMMIT; -- I want to insert these values of column F of table B in table A corresponding -- to column name which is there in table B column D. BEGIN DECLARE @sql LONG VARCHAR; SELECT STRING ( 'INSERT A VALUES ( ', LIST ( B.F ORDER BY SYSCOLUMNS.colno ), ')' ) INTO @sql FROM SYS.SYSCOLUMNS INNER JOIN B ON B.D = SYSCOLUMNS.cname WHERE SYSCOLUMNS.tname = 'A'; EXECUTE IMMEDIATE @sql; COMMIT; SELECT @sql; SELECT * FROM A; END; @sql ------------------------ INSERT A VALUES ( 10,20) X Y ----------- ----------- 10 20 |
Please add an example to make it clear what the inputs are and what you want to happen. My expectation is that you will need to form a string representing the INSERT statement (using the data from the table(s)) and then use EXECUTE IMMEDIATE to execute the statement.
Let's suppose we have 2 tables A and B. Table A has 2 columns X and Y. Table B has 2 columns D and F. There are 2 rows in Table B. Column D of table B has values X and Y i.e. the column name of table A. Column F of table B has values 10 and 20. I want to insert these values of column F of table B in table A corresponding to column name which is there in table B column D. I hope I could explain.