INSERT INTO ... SELECT without detailing all columns

Question

How do you insert selected rows from table_source to table_target using SQL in MySQL where:

Both tables have the same schema
All columns should transfer except for the auto-increment id
Without explicitly writing all the column names, as that would be tedious

The trivial INSERT INTO table_target SELECT * FROM table_source fails on duplicate entries for primary key.

Related: https://stackoverflow.com/a/5253338 – cleybertandre Sep 09 '19 at 14:15 — cleybertandre, Sep 09 '19 at 14:15

score 30 · Accepted Answer · answered Nov 28 '11 at 14:44

30

Either you list all of the fields you want in the insert...select, or you use something else externally to build the list for you.

SQL does not have something like SELECT * except somefield FROM, so you'll have to bite the bullet and write out the field names.

answered Nov 28 '11 at 14:44

Marc B

356,200
43
426
500

2

You're right, in the end I used the explicit syntax. For completeness: easily get the column list using [`DESCRIBE`](http://dev.mysql.com/doc/refman/5.0/en/describe.html) and then use the syntax `INSERT INTO table1 (field1,field2,field3) SELECT table2.field1,table2.field2,table2.field3 FROM table2;` – Jonathan Livni Nov 30 '11 at 09:44
4

Easiest way to get the csv field names: `SELECT CONCAT(GROUP_CONCAT(COLUMN_NAME SEPARATOR ','), "\n") FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename' GROUP BY TABLE_NAME` – Hafenkranich Sep 27 '15 at 20:05

Devart · Answer 2 · 2011-11-28T14:58:18.840

29

Column names have to be specified -

INSERT INTO table_target SELECT NULL, column_name1, column_name2, column_name3, ...
  FROM table_source;

Just pass NULL as a value for the auto-increment id field.

edited Nov 28 '11 at 14:58

answered Nov 28 '11 at 14:45

Devart

119,203
23
166
186

stivlo · Answer 3 · 2011-11-28T14:59:51.683

6

Of course, primary key must be unique. It depends on what you want to achieve, but you could exclude rows with a primary key that already exists.

INSERT INTO table_target SELECT * FROM table_source 
WHERE table_source.id NOT IN (SELECT id FROM table_target)

UPDATE: since you also need the extra rows, you should resolve the conflict first, does table_source have relationships? If not you could change those keys:

UPDATE table_source SET id = id + 1000
WHERE id IN (SELECT id FROM table_target)

Where 1000, is a constant, big enough so they go after the end of your table.

edited Nov 28 '11 at 14:59

answered Nov 28 '11 at 14:44

stivlo

83,644
31
142
199

That's a good workaround only if you don't have foreign keys pointing to `table_source`. Also, if there could be a way to dynamically fetch the largest id in `table_target` instead of using a constant, it would be a slightly better workaround – Jonathan Livni Nov 28 '11 at 15:07

score 4 · Answer 4 · answered Nov 28 '11 at 14:48

Tedious but safe and correct.

Writing INSERT statements without providing a list of columns leads to code that's hard to debug and, more importantly, very fragile code that will break if the definition of the table is changed.

If you absolutely can't write the column names out yourself then it's relatively easy to build a tool into your code that will create the comma-separated list for you.

score 3 · Answer 5 · answered Aug 01 '16 at 11:35

This is my final solution to mass update with 'replace insert' command.

SET @@session.group_concat_max_len = @@global.max_allowed_packet;
SET @schema_db = 'db';
SET @tabl = 'table';
SET @cols = (SELECT CONCAT('`',GROUP_CONCAT(COLUMN_NAME SEPARATOR '`, `'), '`') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema_db AND TABLE_NAME = @tabl GROUP BY TABLE_NAME);
SET @Querystr = CONCAT('REPLACE INTO ',@schema_db,'.',@tabl,' SELECT ', @cols,' FROM import.tbl_', @tabl);

PREPARE stmt FROM @Querystr;
EXECUTE stmt;

score 2 · Answer 6 · answered Nov 28 '11 at 14:44

2

I think you could use syntax like:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

REF: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Hope it helps

answered Nov 28 '11 at 14:44

Sudhir Bastakoti

99,167
15
158
162

Polarfrosch100 · Answer 7 · 2016-08-01T11:36:21.783

It seems as if columns can not be given as a place holder in a MySQL Prepared Statement. I have compiled the following solution for testing:

SET @schema_db = 'DB';
SET @table = 'table';
SET @cols = (SELECT CONCAT(GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '), "\n") FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema_db AND TABLE_NAME = @table GROUP BY TABLE_NAME);
SET @Querystr = CONCAT('SELECT',' ', @cols,' ','FROM',' ',@schema_db,'.',@table,' ', 'Limit 5');
PREPARE stmt FROM @Querystr;
EXECUTE stmt;

score 0 · Answer 8 · answered Aug 19 '20 at 08:24

You can use dynamic query:

DECLARE @Columns VARCHAR(MAX)=''
DECLARE @Query VARCHAR(MAX)=''

SELECT 
       @Columns = ISNULL(@Columns +',', '') + T.COLUMN_NAME
FROM 
( 
    select name as COLUMN_NAME from sys.all_columns
    where object_id = (select object_id from sys.tables where name = 'Source_Table')
    and is_identity = 0
)T


set @Query = 'insert into Target_Table (' + SUBSTRING(@Columns,2 , 9999) + ') select ' + SUBSTRING(@Columns,2 , 9999) + ' from Source_Table';

PRINT @Query
EXEC(@Query)

score 0 · Answer 9 · answered Nov 28 '11 at 14:46

0

INSERT IGNORE just "bypass" the duplicate rows.

http://dev.mysql.com/doc/refman/5.5/en/insert.html

answered Nov 28 '11 at 14:46

Moshe L

1,797
14
19

Wouldn't that be... horrible?! – Jonathan Livni Nov 28 '11 at 14:57
No, if the ignored rows is the rows you need. otherwise, you can delete by ID (or other unique field) and `INSERT`. – Moshe L Nov 28 '11 at 15:08

score 0 · Answer 10 · answered Nov 28 '11 at 20:43

You can probably do it with prepared statements.

PREPARE table_target_insert FROM 'INSERT INTO table_target SELECT ? FROM table_source';
SET @cols:='';
SELECT @cols:=GROUP_CONCAT(IF(column_name = 'id','NULL',column_name) separator ",") FROM information_schema.columns WHERE table_name='table_source';
EXECUTE table_target_insert USING @cols;

Ziad Eldada · Answer 11 · 2018-03-24T17:59:54.387

The easiest way to do it is to use phpmyadmin to write the list of columns, then to change it as needed, in the example below I want to duplicate row with id=1078 and in this table I have id unique auto increment and alias unique.therefore I created my query as follow, with id & alias replaced by a desired value. and it worked like a charm.

INSERT INTO sy3_menuselect 1079, menutype, title, "alias", note, path, link, type, published, parent_id, level, component_id, checked_out, checked_out_time, browserNav, access, img, template_style_id, params, lft, rgt, home, language, client_id from sy3_menuwhere id=1078

Alternatively, to auto increment id, use the following Join statement: INSERT INTO sy3_menuselect * from (SELECT MAX(id+1 )from sy3_menu)a join (select menutype, title, "alias", note, path, link, type, published, parent_id, level, component_id, checked_out, checked_out_time, browserNav, access, img, template_style_id, params, lft, rgt, home, language, client_idfrom sy3_menuwhere id=1079)b

INSERT INTO ... SELECT without detailing all columns

11 Answers11

Linked

Related