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, 12:50

suberta's gravatar image

suberta
1002617
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, 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, 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 

permanent link

answered 20 Apr, 15:32

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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, 12:50

question was seen: 404 times

last updated: 20 Apr, 15:32