Copying / exporting data to an existing table. |
We can export or copy data from one table to another table by using insert command. We can also use replace statement to copy data. We will try with insert command first. The difference between insert and replace statement is the way primary key or the unique key is handled. Here we will copy the date from one table to another table and restrict the data with one where clause added so selective data will be transferred to the second table.To copy the data from one table to another table by creating a new table we have to use create table command. We will use our student table for this. You can download the sql dump file of the table for you use. Here is our original student table.
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 2 |
Max Ruin |
Three |
85 |
| 3 |
Arnold |
Three |
55 |
| 4 |
Krish Star |
Four |
60 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
Here we will create one similar structure table student2 for receiving the data from student. Here is the sql dump to create the student2 table.
CREATE TABLE student2 (
id int(2) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
class varchar(10) NOT NULL default '',
mark int(3) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
Now we will apply this sql command to export data from student table to student2 table
insert into student2 select * from student;
With this command we will copy all data from student to student2 table
We can restrict the data we want to export by adding where clause to the query like this.
insert into student2 select * from student where class = 'Four';
The above query will copy four records from student table to student2 table. Please note that if the student2 table is not empty then the unique constraints for the field id will not allow duplicate id so data will not be updated. Here if we want to update student2 table then we have to use replace command in place of insert command.
replace into student2 select * from student where class = 'Four';
With this 4 records will be updated in student2 table
| nikhil | 20-05-2009 |
|---|
plz tell me about, database?
what is the procedure to copy one database to annother database in mysql. | | shekhar sinha | 06-08-2009 |
|---|
| can constraints be copied from one table to another table? | | shekhar sinha | 06-08-2009 |
|---|
| can only primary key data be deleted or dropped? | | shekhar sinha | 06-08-2009 |
|---|
| can we define more than one primary key in one table? | | prasath | 18-01-2010 |
|---|
| select * into "new table name" from database.dbo.tablename |
|
If you are facing any problem in viewing this page, please tell us
