Insertion in Mysql

Question

Hi friends let me know the way to the issue as follows....... i am trying to insert one table into another table as its column value.... let us say table1 contains 2 columns and table2 contains 3 columns now i need to insert the table1 into the table2 as a value of 3 column,so that in that column table1 should be present.This is perticularly with respect to MYSQL only.please suggest me including the data types of the columns and how can we retrive the data which is stored in 3rdcolumn.

Answer 1

Could you explain a bit please ? (give example)

My guess for now:

INSERT INTO table2 (id1, value1, more_value)
    SELECT id1, value1, NULL FROM table1

Answer 2

If you can write a query which selects what you want from table1, you can use INSERT...SELECT to insert into table2.

For example

INSERT INTO table2(col3) 
SELECT CONCAT(col1,col2) as table1val FROM table1

Answer 3

You cannot insert an entire table as a column value.

If you need to reference the entire table1 in one column, you need to add some sort of "group id" to table 1, then insert that into table 2 and do a JOIN on SELECT.

For example if you wanted the below table to be a value in table 2:

original table1:
col1,   col2
==============
"val1", "val5"
"val2", "val6"
"val3", "val7"
"val4", "val8"

new table1:
col1,   col2,   group_id
========================
"val1", "val5", 1
"val2", "val6", 1
"val3", "val7", 1
"val4", "val8", 1

Then in table2 you would have

table2:
col1, col2, group_id
====================
"x",  "y",  1

And to get that back you would do something along the lines of:

SELECT
  table2.col1
  , table2.col2
  , table1.col1
  , table1.col2
FROM
  table2
  INNER JOIN
  table1
  ON (table2.group_id = table1_group_id)

And that would give you:

table2.col1, table2.col2, table1.col1, table1.col2
==================================================
"x",         "y",         "val1",      "val5"
"x",         "y",         "val2",      "val6"
"x",         "y",         "val3",      "val7"
"x",         "y",         "val4",      "val8"