Student table with 35 records
Student2 table with 0 records
With this command we will copy all data from student to student2 table
INSERT INTO student2 SELECT * FROM student;
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.
INSERT INTO student2 SELECT * FROM student WHERE class = 'Four';
WE can use selected columns to insert the records, here is an example.
REPLACE INTO student2 SELECT * FROM student WHERE class = 'Four';
In above query we have not included gender column and this column accepts NULL as default value ( or it has some other default value ).
INSERT INTO student2 (id,name,class,mark) SELECT id,name,class,mark FROM student
This query will generate the error like this as id =3 is already exist in student2 table and that will violate the unique constraint.
INSERT INTO student2 (id,name,class,mark,gender) SELECT id,name,class,mark,gender FROM student WHERE student.id=3
Now let us try this query
#1062 - Duplicate entry '3' for key 'PRIMARY'
The above query will insert the record and update the mark column to 5, this way we can update records using on duplicate key command.
INSERT INTO student2 (id,name,class,mark,gender) SELECT id,name,class,mark,gender FROM student WHERE id=3 ON DUPLICATE KEY UPDATE mark=5
INSERT INTO student2(id,name,class,mark,gender) SELECT id,name,class,mark,gender FROM student WHERE class='Four' ON DUPLICATE KEY UPDATE mark=200,gender=student.gender;
INSERT INTO plus2_inv_stock (p_id,qty,price_sell) SELECT p_id ,0,0 FROM `plus2_inv_products`
|plz tell me about, database? what is the procedure to copy one database to annother database in mysql.|
|can constraints be copied from one table to another table?|
|can only primary key data be deleted or dropped?|
|can we define more than one primary key in one table?|
|select * into "new table name" from database.dbo.tablename|
|can you help me with copying data from table1 to table2 for example in postgres database..|
|can we copy content of a table to another table using |||
|Could any one tell me the answer how to Create one table from another table without copying the data from the first table.|
|I really like the REPLACE SELECT statement.|