SQL: How to Copy the Database Table Definition and Data Using SQL Scripts

Here are simple, yet powerful SQL scripts to copy the entire definition and data of a database table. These SQL scripts are useful if you are making a backup of a table without a GUI. The only drawback of using these, is that it doesn't copy the keys and constraints of the table. But personally, I prefer using these scripts when I make a backup than using a GUI because it's so simple and I get to practice my SQL :). See below.

First, let's create a table for this tutorial using SQL.

CREATE TABLE IF NOT EXISTS `employees` (
  `employee_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone_number` varchar(15) NOT NULL,
  PRIMARY KEY  (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121;

Then, we insert records to the table using SQL again.

INSERT INTO `employees` (
`employee_id`, `first_name`, `last_name`, `email`, `phone_number`) 
VALUES
(100, 'Steven', 'King', 'SKING', '515.123.4567'),
(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568'),
(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569'),
(103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567'),
(104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568'),
(105, 'David', 'Austin', 'DAUSTIN', '590.423.4569'),
(106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560'),
(107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567'),
(108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569'),
(109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169'),
(110, 'John', 'Chen', 'JCHEN', '515.124.4269'),
(111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369'),
(112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469'),
(113, 'Luis', 'Popp', 'LPOPP', '515.124.4567'),
(114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561'),
(115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562'),
(116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563'),
(117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564'),
(118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565'),
(119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566'),
(120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234');

Now, let's copy the table data including the definition.

CREATE TABLE emp AS SELECT * FROM employees

Here's to copy the table definition without the data

CREATE TABLE emp AS SELECT * FROM employees WHERE 1 = 0

And here's to copy the seleted table data including the definition using MySQL's LIMIT

CREATE TABLE emp AS SELECT * FROM employees LIMIT 0, 10

And the last but not least, let's copy the seleted table column including the definition

CREATE TABLE emp AS SELECT first_name, last_name FROM employees

The last four SQL scripts about creates a table named emp from the result of the SELECT statement.

There are other variations of the CREATE TABLE [table] AS SELECT statements that I haven't included here, some of them I don't know yet. I'll update this post as soon as I find more.