#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Merge 1 table from 2 differents databases


    Hello,

    I would like to merge the table t_vaccination located in a source database(server 127.0.0.1 and port 2007) with the table t_vaccination located in the final database(server 127.0.0.1 and port 2008) and the merge result has to be in the final database
    Here is the structure of the t_vaccination table:


    Code:
    CREATE TABLE `t_vaccination` (
    	`nIdVaccination` INT(10) UNSIGNED NOT NULL,
    	`nIdVaccin` INT(10) UNSIGNED NULL DEFAULT NULL,
    	`nIdVacProtocole` INT(10) UNSIGNED NULL DEFAULT NULL,
    	`nIdPatient` INT(10) UNSIGNED NOT NULL,
    	`nIdUtilisateur` INT(10) UNSIGNED NULL DEFAULT NULL,
    	`sDateInjection` VARCHAR(8) NOT NULL DEFAULT '',
    	`nNumInjection` VARCHAR(45) NOT NULL DEFAULT '0',
    	`sNumLot` VARCHAR(45) NOT NULL DEFAULT '',
    	`nRappel` TINYINT(4) NOT NULL DEFAULT '0',
    	`sLibelle` VARCHAR(255) NOT NULL DEFAULT '',
    	`sAction` VARCHAR(16) NOT NULL DEFAULT 'A 20080101000000',
    	`sDH_REPLIC` DATETIME NULL DEFAULT '2010-01-01 00:00:00',
    	PRIMARY KEY (`nIdVaccination`),
    	INDEX `NDX_t_vaccination_nIdUtilisateur` (`nIdUtilisateur`),
    	INDEX `NDX_t_vaccination_nIdVaccin` (`nIdVaccin`),
    	INDEX `NDX_t_vaccination_nIdVacProtocole` (`nIdVacProtocole`),
    	INDEX `NDX_t_vaccination_nIdPatient` (`nIdPatient`),
    	CONSTRAINT `FK_vaccination_nIdUtilisateur_utilisateur` FOREIGN KEY (`nIdUtilisateur`) REFERENCES `t_utilisateur` (`nIdUtilisateur`),
    	CONSTRAINT `FK_vaccination_nIdVaccin_vaccin` FOREIGN KEY (`nIdVaccin`) REFERENCES `t_vaccin` (`nIdVaccin`)
    )
    this table has a link with the next tables:
    1 - t_utilisateur table by the nIdUtilsateur field
    2 - t_vaccin table by the nIdVaccin field
    3 - t_vac_protocole table by the nIdVacProtocole field
    4 - t_patient table by the nIdPatient field

    What is the sql request to write in order to merge the t_vaccination from these 2 differents databases ?

    Thanks for your help

    Regards

    Motti
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    INSERT
      INTO finaldatabase.t_vaccination
    SELECT *
      FROM sourcedatabase.t_vaccination
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    merge


    Hello r937,

    Thank you for your response, i have only one problem.
    How i translate the source database that is located on the host = 127.0.0.1 and port = 2007 in the sql command
    because the 2 databases have the same name = crp_db.
    the source database located on the host = 127.0.0.1 port = 2007
    the final database located on the host = 127.0.0.1 port = 2008
    i can't write
    INSERT
    INTO crp_db.t_vaccination
    SELECT *
    FROM crp_db.t_vaccination

    how to translate the host and the port of the source database to sql command
    what i have to declare in order that the source database point to the crp_db with host = 127.0.0.1 and port = 2007 and the final database point to the crp_db with host = 127.0.0.1 and port = 2008 ?

    Regards

    Motti

IMN logo majestic logo threadwatch logo seochat tools logo