Solved

copy data from one identical table into another.

Posted on 2014-02-15
8
210 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
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Dan Craciun
Comment Utility
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
 

Author Comment

by:rgb192
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Expert Comment

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

Author Comment

by:rgb192
Comment Utility
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 34

Accepted Solution

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

Author Closing Comment

by:rgb192
Comment Utility
works great. thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now