INSERT INTO... SELECT без детализации всех столбцов

Как вы вставляете выбранные строки из table_source в table_target с помощью SQL в MySQL, где:

Обе таблицы имеют одну и ту же схему
Все столбцы должны переноситься, за исключением автоматического приращения id
Без явного написания всех имен столбцов, поскольку это было бы утомительно

Тривиальный INSERT INTO table_target SELECT * FROM table_source терпит неудачу при дублировании записей для первичного ключа.

Ответы

Ответ 1

Либо вы перечислите все поля, которые хотите вставить... выберите, либо вы используете что-то еще извне для создания списка для вас.

SQL не имеет что-то вроде SELECT * except somefield FROM, поэтому вам придется укусить пулю и записать имена полей.

Ответ 2

Необходимо указать имена столбцов -

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

Просто передайте значение NULL в качестве значения для поля auto-increment id.

Ответ 3

Трезвый, но безопасный и правильный.

Написание инструкций INSERT без предоставления списка столбцов приводит к тому, что код, который трудно отлаживать и, что более важно, очень хрупкий код, который сломается при изменении определения таблицы.

Если вы абсолютно не можете сами писать имена столбцов, то относительно легко создать инструмент в своем коде, который будет создавать список разделенных запятыми.

Ответ 4

Конечно, первичный ключ должен быть уникальным. Это зависит от того, чего вы хотите достичь, но вы можете исключить строки с основным ключом, который уже существует.

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

UPDATE: поскольку вам также нужны дополнительные строки, сначала нужно разрешить конфликт, есть ли у table_source отношения? Если вы не можете изменить эти клавиши:

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

Где 1000, является константой, достаточно большой, чтобы они шли после конца вашей таблицы.

Ответ 5

Я думаю, вы могли бы использовать синтаксис вроде:

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 Надеюсь, что это поможет.

Ответ 6

Это мое окончательное решение для массового обновления командой "replace insert".

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;

Ответ 7

INSERT IGNORE просто "обходит" повторяющиеся строки.

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

Ответ 8

Возможно, вы можете сделать это с помощью подготовленных операторов.

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;

Ответ 9

Кажется, что столбцы не могут быть указаны как владелец места в заявлении, подготовленном MySQL. Я собрал следующее решение для тестирования:

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;