23

I'm sure this is simple I just cant get the correct search terms to find the answer. But I have one table for locations with IDs

ID| Name 1 | Foo 2 | Bar 3 | Blah

and another table (table2) that has a field referencing those location IDs:

ID| LocationID | Foo | Bar 1 | 1 | ... | ... 2 | 2 | .. 3 | 5 | ...

Where LocationId = a value from location. What I want to do is for every ID in location (1,2,3...) add a record to the other table. So for example: 

"insert into table2 (locationID, foo, bar) values (1, "hello", "world");" "insert into table2 (locationID, foo, bar) values (2, "hello", "world");"

and so on..

Improve this question

2 Answers 2

Reset to default
51

You can use INSERT INTO ... SELECT, so for your example

INSERT INTO table2 (locationID, foo, bar) SELECT ID, 'hello', 'world' FROM table1
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Oh ok, I was trying something like that but I figured "Select ID, 'Hello', 'world'" was trying to select all three of those values from the 1st table not just the ID.
SELECT ID, 'hello', 'world' maybe seems a bit weird but selects the column ID and the plain values 'hello' and 'world'. You can see for yourself by executing SELECT ID, 'hello', 'world' FROM table1 (so the part without the INSERT INTO) to check whats will be inserted.
2

You can do an Insert... Select

Insert Table2 (LocationID, Foo, Bar) Select ID, "Hello", "World" From Location
Improve this answer

1 Comment

Insert into instead of just Insert, and ommit the values

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.