PHP —

Transferring data between databases and DBMSs

For many databases-particularly those in which legacy data is being redeployed into a DBMS-most of the data insertion occurs as the database is created. A common approach is to create a script that contains SQL statements that can be repeatedly replayed; it's the approach we used to create the winestore database. This has the advantage that the script can be run on many different DBMSs, and it makes migration easier than with the LOAD DATA INFILE approach.

To remove and partially rebuild the winestore database, we might author a script containing the statements shown in Example 3-3.

Example 3-3. Script for creating and inserting winestore data
DROP DATABASE winestore;
CREATE DATABASE winestore;
use winestore
CREATE TABLE customer (
  cust_id int(5) NOT NULL auto_increment,
  surname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20),
  phone varchar(15),
  fax varchar(15),
  email varchar(30) NOT NULL,
  birthdate date(  ),
  salary int(7),
  PRIMARY KEY (cust_id),
  KEY names (surname,firstname)
);
INSERT INTO customer VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs','171 Titshall
Ccl','','','St Albans','WA', '7608','Australia','(618)63576028','', 'Dimitria@Lucaston.
com','1969-08-11',35000);
INSERT INTO customer VALUES (NULL,'LaTrobe','Anthony', 'Y','Mr','125 Barneshaw
St','','','Westleigh','WA','865
5','Australia','(618)73788578','(618)73786674', 'Anthony@Karumba.com','1952-03-10',54000);
INSERT INTO customer VALUES (NULL,'Fong','Nicholas','K','Mr','99 Kinsala Pl',
'','','Stormlea','NSW','6400','Australia',
'(612)85534220','(612)85535180','Nicholas@Torquay.com',
'1942-06-29',170000);
INSERT INTO customer VALUES (NULL,'Stribling','James','','Mr','6 Woodburne Pl','','',
'Legana','QLD','6377','Australia','(617)66603522', '','James@Murrabit.com', '1943-11-
22',25000);

The script in Example 3-3, which has been saved to a file winestore.database, can be replayed using the MySQL command and a shell redirection:

% mysql -ppassword < winestore.database

This script runs the command interpreter with the statements and commands listed in the file winestore.database.

Data that is already managed in a MySQL database can be extracted using the utility mysqldump:

% mysqldump -ppassword winestore > winestore.database

The statements to DROP, CREATE, and use the database can be manually added with an editor to permit replaying of the script. We manually added the first three lines of Example 3-3 after using mysqldump to create the script.

To use the script to create a duplicate database, winestore2, for testing, you can change the first three lines of Example 3-3 to:

DROP DATABASE winestore2;
CREATE DATABASE winestore2;
use winestore2