0

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.

Funlamb
  • 551
  • 7
  • 20

1 Answers1

0

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;
forpas
  • 160,666
  • 10
  • 38
  • 76
  • That's it. I guess I was focused on using VALUES too much. Why does it work if I'm only entering one row at a time? – Funlamb Aug 17 '22 at 19:56
  • @Funlamb because VALUES expects a list of tuples like: `insert into workouts (userid, dateandtime) values (2, "2022-01-02T06:00"), (3, "2022-01-03T06:00"),...;` – forpas Aug 17 '22 at 19:58
  • Now I'm trying to do the same things with `sets` and `sets2`. The problem is I have added the id's from `sets` to `sets2`. Using `INSERT INTO sets (interval, resistance, setnumber, workoutid, exerciseid) select (interval, resistance, setnumber, workoutid, exerciseid) from sets2;` gives me an error of `in prepare, row value misused (1)`. I was hoping your answer would help me for this problem too. What is going wrong here? – Funlamb Aug 17 '22 at 20:10
  • @Funlamb remove the parentheses from: `select (interval, resistance, setnumber, workoutid, exerciseid) from sets2` – forpas Aug 17 '22 at 20:11
  • Yeah. I just found it online. I should have looked before asking but wanted to catch you while you were still on here. Thanks for your help. – Funlamb Aug 17 '22 at 20:13