Posted on 02/02/2009. By Pete Otaqui.
I had the need to copy a load of data from one table to another while incrementing (or just adding an arbitrary number to) some numeric indices.
Before I lept to a programming language like PHP to do a load of selects, manipulate the data and then run inserts I thought I’d try and do it in pure SQL. It turns out this is fairly straightforward.
Let’s assume we have two databases, db_source and db_target and that we want to copy stuff from the former into the latter. Both of these have identical tables called ‘users’ and ‘posts’ like so:
USERS
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
POSTS
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT(11) NOT NULL,
title VARCHAR(255),
post TEXT
Now, let’s assume that in db_target we *already* have some posts and some users, and what we really want to do is merge the records from db_source with the ones that already in db_target, obviously maintaining all the related keys.
In this example we will say that we have 5 current users in db_target, and 100 posts; and also that the auto_increment values are 1 to 1 in both tables (that is the next auto_increment value will be 6 and 101 for users and posts respectively in db_target).
With these two commands, which will run SO much faster than some kind of programmatic loop, we can copy all the users and posts at a stroke without losing any associations:
# copy the users, adding 5 to the user id while we’re at it:
INSERT INTO db_target.users ( id, name ) SELECT (id+5) AS id, name FROM db_source.users;
# copy the posts, adding 5 to the user_id and 100 to the post id:
INSERT INTO db_target.posts ( id, user_id, title, post ) SELECT (id+100) AS id, (user_id+5) AS user_id, title, post FROM db_source.posts;
Note that I have selected “as” where I have been using the addition operator. Given that we are also specifying which columns we are updating I’m not 100% sure that it’s necessary, but I find it helpful anyway.