How to insert a new Dataset in a temporary Table and get the new id using Mysql?

Question

I try to insert Data in the temporary Table, but when i call select last_insert_id() it return allways the same number. I have set the autoincement setting.

In the normal Table works, but when i Copy the normal Table to a temporary Table, then i have the problem with the select last_insert_id().

This is my code:

CREATE TEMPORARY TABLE IF NOT EXISTS suchergebnisse_temp (SELECT * from suchergebnisse);
INSERT INTO suchergebnisse_temp SET datensatzid='2865', datum='2015-05-13 00:00:00', tabelle='task', sortierung1='1';
SELECT LAST_INSERT_ID();

The Normal Table:

CREATE TABLE IF NOT EXISTS `suchergebnisse` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tabelle` varchar(100) NOT NULL DEFAULT '0',
  `datensatzid` bigint(20) NOT NULL DEFAULT '0',
  `datum` datetime NOT NULL,
  `sortierung1` int(11) NOT NULL COMMENT 'Priorität',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Temporäre Tabelle.';

Can you help me? Thanks :)

Answer 1

I forgot to add the key explicit to the temporary Table. It needs to execute the following code:

ALTER TABLE `suchergebnisse_temp`
    CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (`id`);

Because with:

CREATE TEMPORARY TABLE IF NOT EXISTS suchergebnisse_temp (SELECT * from suchergebnisse);

copys only the Field Data but not the Key Data. The Key Data must be declared separately.