0

In MySQL, you can write something like

INSERT INTO t1 (col1) SELECT col1 FROM t2

To copy some data over. What if I want to copy some data over from multiple tables? Can I write something like

INSERT INTO t1 (col1) SELECT col1 FROM t2, SELECT col1 FROM t3

?

mpen
  • 272,448
  • 266
  • 850
  • 1,236

1 Answers1

2

i think it should be

INSERT INTO t1 (col1) SELECT col1 FROM t2 UNION SELECT col1 FROM t3

EDIT: Now before you go copying data, you may want to verify using

UNION vs UNION ALL

UNION will remove duplicates in the data. UNION ALL will produce a simple concatenation of the two result sets.

Simon
  • 9,197
  • 13
  • 72
  • 115
  • Seems to work. This'll save me some code duplication, thanks :) – mpen Mar 02 '11 at 21:19
  • how is a "duplicate" defined? as in all the selected columns have to be the same? or every column in that table, even the non-selected ones? – mpen Mar 02 '11 at 21:45
  • the duplicates will be removed simply mean that it will concatenate all values from t1 and t2, then remove duplicates (DISTINCT). UNION ALL simply concatenates the two result sets from t1 and t2. See http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Simon Mar 02 '11 at 21:55
  • Also, UNION ALL can be a lot faster if you've got a lot of data. – Parris Varney Mar 02 '11 at 22:20