Inserting rows from table2 to end of table1 with unique ids-CodePudding

I'm trying to add a test user to my website that employers can look at to see my work. I want to use some of the data I have entered into my profile so that it is faster.

I have a workouts table:

CREATE TABLE workouts(
    id INTEGER NOT NULL,
    userID INTEGER NOT NULL,
    DateAndTime smalldatetime NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (UserID) REFERENCES users(id)
);

I have taken 25 of the first results and put it into a temporary workouts2 table:

CREATE TABLE workouts2 (
    userid integer,
    dateandtime smalldatetime);

Now I want to take those rows from workouts2 and put them into workouts. I have tried to add them by inserting workouts2 into workouts like this:

insert into workouts (id , userID, DateandTime) values (select * from workouts2);

This gives me an Error: in prepare, near "select": syntax error (1)

I can do it one at a time like this:

insert into workouts (userid, dateandtime) values (2, "2022-01-02T06:00");

Doing it one at a time is not ideal.

What am I missing here? I know I have a syntax error but I don't know how to fix it.

I have looked at this question which inserts one at a time: How to insert a unique ID into each SQLite row?

The problem is it only inserts one at a time.

CodePudding user response：

You should use SELECT instead of VALUES and not include the column id, which is auto-incremented, in the list of columns of workouts which will receive the values (such a column does not exist in workouts2):

INSERT INTO workouts (userID, DateandTime) 
SELECT * 
FROM workouts2;