INSERT INTO... SELECT.... FROM

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

INSERT INTO... SELECT.... FROM

Post by neophyte »

I'm working on copying some data from one db to another. The sql I've written (INSERT INTO SELECT FROM) works but on record 19 it says

'' duplicate entry for key 2.

The next record is 20 not '' in the table I'm copying from. I'm confused on why this is happening and how do I fix it or work around it.

Any ideas?

Thanks 0 plenty!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

want to post the query?
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Sure

Post by neophyte »

I'll post it....

Code: Select all

INSERT INTO mad_users ( username, pwd, firstname, lastname, email) SELECT  ddl_user.username, ddl_user.password, ddl_user.firstname, ddl_user.lastname, ddl_user.email  FROM ddl.ddll_user
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

which fields in the insert are unique/primary keys? is this same field a unique/primary key in the selection table?
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

There is only one key in each table. In both cases it's the 'id' field. I dropped them from the query because in both cases the are also auto_incrementing. The table I'm copying from has about 40 records and I checked the numbers to make sure they are sequential and that they are all there. It checked out.
Post Reply