Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables:

CREATE TABLE IF NOT EXISTS `mo_sms` (
  `id_MO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `precio` float(11,2) unsigned DEFAULT '0.00' COMMENT 'Precio del MO',
  `id_user` int(10) unsigned NOT NULL,
  `state` char(1) NOT NULL DEFAULT '0' COMMENT '0=Por procesar 1=Procesado',
  `tipo_sms` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MO`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_sms_proceso_state` (`sms_proceso`,`state`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`),
  KEY `ix_varios` (`anio_sms`,`mes_sms`,`comando_id`,`shortcode_id`,`brand_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MO' AUTO_INCREMENT=82972 ;

CREATE TABLE IF NOT EXISTS `mt_sms` (
  `id_MT` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL DEFAULT 'c' COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `tipo_sms` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `id_MO` int(11) unsigned NOT NULL,
  `state` char(1) DEFAULT '0' COMMENT '0=Por Procesar 1=Procesado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MT`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MT' AUTO_INCREMENT=93853;

I have some values in DB2 that should be migrated (inserted) in DB1. My problem here is I don't know how to get the records from DB2.mo_sms tables and those relations from DB2.mt_sms and then insert to DB1.mo_sms and DB2.mt_sms. For example I'll get all the records from mo_sms with fecha_sms LIKE '%2013-04-19%' then if I insert those records in DB1.mo_sms new IDs will be generated then the integrity will be lost. Should I make this programatically or there is any way to do this using just SQL queries? I'm using MySQL as DBMS.

share|improve this question
    
is mt_sms.id_MO the only relationship between this 2 tables ? –  altmannmarcelo Apr 25 '13 at 9:27
    
@altmannmarcelo yes, it's the only relationship betwenn the tables –  Reynier Apr 25 '13 at 13:24

3 Answers 3

Data Extraction into a Sandbox

CREATE DATABASE IF NOT EXIST sms_sandbox;
CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms;
CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms;
#
# Drop All Indexes From the Sandbox
#
ALTER TABLE sms_sandbox.mo_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_sms_proceso_state`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`,
    DROP INDEX `ix_varios`;
ALTER TABLE sms_sandbox.mt_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`;
ALTER TABLE sms_sandbox.mo_sms AUTO_INCREMENT=1;
ALTER TABLE sms_sandbox.mt_sms AUTO_INCREMENT=1;
#
# Load the Data From 2013-04-19
#
INSERT INTO sms_sandbox.mo_sms
SELECT * FROM DB2.mo_sms
WHERE fecha_sms >= '2013-04-19 00:00:00'
AND fecha_sms <= '2013-04-19 23:59:59';
INSERT INTO sms_sandbox.mt_sms
SELECT * FROM DB2.mt_sms A INNER JOIN sms_sandbox.mo_sms B USING (id_MO);

Data Insertion From the Sandbox

#
# Get Maximum id_MO from DB1.mo_sms
# Increment all id_MOs by the Max Value
#
SELECT MAX(id_MO) INTO @Max_id_MO FROM DB1.mo_sms;
UPDATE sms_sandbox.mo_sms SET id_MO = id_MO + @Max_id_MO;
UPDATE sms_sandbox.mt_sms SET id_MO = id_MO + @Max_id_MO;
#
# Load the New Data into DB1
#
ALTER TABLE DB1.mo_sms DISABLE KEYS;
INSERT INTO DB1.mo_sms SELECT * FROM sms_sandbox.mo_sms;
ALTER TABLE DB1.mo_sms ENABLE KEYS;
ALTER TABLE DB1.mt_sms DISABLE KEYS;
INSERT INTO DB1.mt_sms
(`sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
`fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
`id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`)
SELECT 
    `sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
    `fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
    `id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`
FROM sms_sandbox.mt_sms;
ALTER TABLE DB1.mt_sms ENABLE KEYS;

WARNING : Practice this on a Staging Server, Please.

SUGGESTION

All the mt_sms tables should have an index on id_MO

ALTER TABLE mt_sms ADD INDEX (id_MO);

Give it a Try !!!

share|improve this answer

You basically need to keep track the new and old id from mo_sms

  1. load all DB2.mo_sms that you want to copy
  2. for each row in this result set
    1. store the actual DB2.mo_sms id
    2. save this row on DB1.mo_sms
    3. store the new DB1.mo_sms id, you can use LAST_INSERT_ID()
    4. load all DB2.mt_sms entries related to id that you stored on step 2.1
    5. save all entries on DB1.mt_sms changing the mt_sms.id_MO to the new id received on DB1 (the one which you stored on 2.3)

This should work.

Edit 1:

You can do it using an external programming language or stored procedure, if you choose to use a stored procedure, you will need to deal with cursors

share|improve this answer
    
yes, you're right this are the basic steps I must follow but my question here is: it's possible to do this only using SQL queries or I need to handle trough code using any programming language? –  Reynier Apr 25 '13 at 16:11

You could also use a tool like Liquibase to generate a data diff from your source and then apply it to the destination database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.