asked on

copy data from one identical table into another.

CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) DEFAULT NULL,
  `profile_id` varchar(20) DEFAULT NULL,
  `sender` varchar(20) DEFAULT NULL,
  `message_id` bigint(20) DEFAULT NULL,
  `message_text` varchar(1000) DEFAULT NULL,
  `dateAgo` datetime DEFAULT NULL,
  `message_read` tinyint(4) DEFAULT NULL,
  `this_user` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2979 DEFAULT CHARSET=utf8;

Open in new window

CREATE TABLE `a_messages_sandbox` (
  `a_messages_id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) DEFAULT NULL,
  `profile_id` varchar(20) DEFAULT NULL,
  `sender` varchar(20) DEFAULT NULL,
  `message_id` bigint(20) DEFAULT NULL,
  `message_text` varchar(1000) DEFAULT NULL,
  `dateAgo` datetime DEFAULT NULL,
  `message_read` tinyint(4) DEFAULT NULL,
  `this_user` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=805 DEFAULT CHARSET=utf8;

Open in new window

Please show me how to copy all the data (not keys) from a_messages_sandbox to final destination table a_messages

I would have just done inserts from backup.sql but keys and autoincrement is in the insert so I can not do

So I had to recreate the table with a different name.

Dan Craciun

INSERT INTO `a_messages` (`conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`)
SELECT `conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`
FROM `a_messages_sandbox`

Open in new window

HTH,
Dan

rgb192

ASKER

Error Code: 1062. Duplicate entry '4624551844' for key 2

maybe try insert ignore or replace

because some inserts are duplicate but I want to do many inserts

Dan Craciun

That means you already have data into your a_messages.

If you want to skip existing data use:

INSERT INTO `a_messages` (`conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`)
SELECT `conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`
FROM `a_messages_sandbox`
ON DUPLICATE KEY IGNORE

Open in new window

rgb192

ASKER

INSERT INTO `a_messages` (`conversation_id`, `profile_id`, `sender`, `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`)
SELECT `conversation_id`, `profile_id`, `sender`, `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`
FROM `a_messages_sandbox`
ON DUPLICATE KEY IGNORE

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNOREINSERT INTO `a_messages` (`conversation_id`, `profile_id`, `sender`, `m' at line 4

I also tried 'ignore insert'
'insert ignore'

Dan Craciun

Yeah, my mistake. The syntax is
INSERT IGNORE INTO
SELECT
FROM

rgb192

ASKER

INSERT IGNORE INTO `a_messages` (`conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`)
SELECT `conversation_id`,  `profile_id`,  `sender`,  `message_id`, `message_text`, `dateAgo`, `message_read`, `this_user`
FROM `a_messages_sandbox`
ON DUPLICATE KEY IGNORE

Open in new window

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE' at line 4

ASKER CERTIFIED SOLUTION

Dan Craciun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

works great. thanks