Solved

copy data from one identical table into another.

Posted on 2014-02-15
8
216 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Very Large data in MYSQL 7 106
Giant ibd file for our biggest table on mysql 2 29
Concat multiple records into one line 3 69
MySqli Real Escape String and SQL Injection 1 79
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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