Solved

copy data from one identical table into another.

Posted on 2014-02-15
8
217 Views
Last Modified: 2014-02-17
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.
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39861842
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
0
 

Author Comment

by:rgb192
ID: 39862697
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
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862719
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

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:rgb192
ID: 39862740
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'
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862890
Yeah, my mistake. The syntax is
INSERT IGNORE INTO
SELECT
FROM
0
 

Author Comment

by:rgb192
ID: 39863592
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
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39863874
Remove the last line.
You don't need ON DUPLICATE KEY if you specify IGNORE after INSERT
0
 

Author Closing Comment

by:rgb192
ID: 39864652
works great. thanks
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question