Skip to content
Tags

,

insert select: copy contents from one table to another

September 25, 2012

Today I had to copy contents from one table to another. Both the tables were set up identically.

Google gave me this
http://www.w3schools.com/sql/sql_select_into.asp

but MySQL doesn’t allow that query. MySQL provides a ‘INSERT (INTO) … SELECT’
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

mysql> insert into about_pages select * from about_us_pages;
Query OK, 72 rows affected (0.18 sec)
Records: 72 Duplicates: 0 Warnings: 0

in the case the tables are not identical you could try:

INSERT INTO resource_images (resource_id, image_file_name, image_content_type, image_file_size, image_updated_at, created_at, updated_at) SELECT id, image_file_name, image_content_type, image_file_size, image_updated_at, NOW(), NOW() FROM resources WHERE image_file_name IS NOT NULL;
Query OK, 263 rows affected (0.08 sec)
Records: 263  Duplicates: 0  Warnings: 0
Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: