CODEWITHSUNDEEP
mysqlsqlsql-server
user4096916
user4096916

Reputation:

SQL, how to insert all rows of a table into another one

OK so I suck at SQL and I always have to ask for help, let me show my problem. I have 2 tables, here is the structure for both:

Now I need to move all rows of one table to the other one (not update), just to have only one table with all rows of both. The ID column is a primary key with auto increment, but when I tried to use INSERT INTO table1 SELECT * FROM table2, sql generates this error #1062 - Duplicate entry '1' for key 'PRIMARY'.

Upvotes: 0

Views: 274

Answers (2)

Undo
Undo

Reputation: 25697

If you don't care about the ID from the old table transferring across, you can use this:

INSERT INTO newTable (courseID, course, bookmark, course_date, posttest, post_attempts)
SELECT courseID, course, bookmark, course_date, posttest, post_attempts
FROM oldTable;

This will transfer all columns except ID, which should solve the issue.

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

If table1 has an identity on ID, you should just select all columns except the ID

INSERT INTO table1(
    course_id,
    course,
    bookmark,
    course_date,
    posttest,
    post_attempts
)
SELECT
    course_id,
    course,
    bookmark,
    course_date,
    posttest,
    post_attempts
FROM table2

Upvotes: 2

Related Questions