Solved

copy data from one identical table into another.

Posted on 2014-02-15
8
213 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
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 34

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 34

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 34

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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