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

 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? asked 20 Apr '17, 12:50 suberta 100●4●9●17 accept rate: 0% 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. (20 Apr '17, 12:56) Mark Culp 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. (20 Apr '17, 13:53) suberta

 ```-- 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 ``` answered 20 Apr '17, 15:32 Breck Carter 27.1k●455●622●894 accept rate: 21%
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×29

question asked: 20 Apr '17, 12:50

question was seen: 434 times

last updated: 20 Apr '17, 15:32