Insert data in a table from another table with column values in 2nd table

[Former Member]

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?

[MarkCulp]

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.

[Former Member]

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.

[Breck_Carter · Accepted solution]

-- 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