Mysql Query - Copy resultset from one database to another

I have a large db that I am chopping into smaller databases based on time intervals. This will reduce query time dramatically. In a query can I copy a resultset from开发者_开发知识库 one database to another with an identical schema?

Basically a select followed by an update conducted in the same code block?

Thanks,

slothishtype

Copying data from one database into another should be almost as simple as @slotishtype describes except you'll need to qualify it with the OTHER database you want it replicated into.

create table OtherDatabase.Student Select * from FirstDatabase.student

However, as you mention about copying same schema, that is something else. If you want all your R/I rules, triggers, etc, you may have to dump the database schema from your first (where it has all the create tables, indexes, etc) and run in a new database. However, that might post an issue where you have auto-incrementing columns. You can't write to a read-only auto-increment column -- the database controls that. However, if such case existed, you would have to just make those columns as integer datatypes (or similar) and do a

insert into OtherDatabase.Student ( field1, field2, etc )
   select field1, field2, etc from FirstDatabase.student 

If it is not necessary to add it to a new database, this will do fine:

CREATE TABLE student1 SELECT * FROM student

EDIT: for the record: This will not coopy over indices etc.

This, however, will:

CREATE TABLE student_new LIKE student; INSERT recipes_new SELECT * FROM student;

slotishtype