SQL, how to INSERT INTO table from a specific start number ID

I need to insert some rows from one table to another, here is the structure for both tables:

ID (int)

courseID (int)

course (bit)

bookmark (varchar(100))

course_date (datetime)

posttest (bit)

post_attempts (int)

The ID column is a primary key with auto increment, so if I use INSERT INTO table1 SELECT * FROM table2 , sql generates this error #1062 - Duplicate entry '1' for key 'PRIMARY' .

Well I can omit the ID column but the problem is that new ID is autogenerated. I have ID references in another table so my question is: Is there a way to specifiying a START number for the ID when I type the command below?

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

---

To increase the ID value for new inserted data do this:

INSERT INTO table1(
    ID,
    course_id,
    course,
    bookmark,
    course_date,
    posttest,
    post_attempts
)
SELECT
    ID + 1000 AS ID,
    course_id,
    course,
    bookmark,
    course_date,
    posttest,
    post_attempts
FROM table2

But if you want to skip some starting values you can use WHERE clause when selecting:

...
FROM table2
WHERE ID > 10

---

You can set the auto_increment start value as follow:

ALTER TABLE table1 AUTO_INCREMENT = 1;

For further information please go here

链接地址: http://www.djcxy.com/p/94294.html

上一篇: 查询示例请求

下一篇: SQL，如何从特定的起始号码ID中插入表