How can I SQL insert a record for every id in another table?

Question

I've seen the insert select but I only want to get one value from the second table. Here's what I'm thinking:

insert into table1
Values((select distinct id from table2),
"blah", "blah", "blah")

So after the Id, the rest of the values will be hard coded to whatever I want. Is this possible?

score 8 · Accepted Answer · answered Aug 19 '16 at 11:26

8

The correct syntax does not use values:

insert into table1(col1, . . . )
    select distinct id, 'blah', 'blah', 'blah'
    from table2;

Notes:

You should always use explicit column lists when using insert, unless you really, really know what you are doing.
Use single quotes to delimit a string, not double quotes.

answered Aug 19 '16 at 11:26

Gordon Linoff

1,242,037
58
646
786

wow it's that easy, I feel bad – Pittfall Aug 19 '16 at 11:36
1

@Pittfall . . . Never feel bad when you learn something . . . at least if no one else gets hurt. – Gordon Linoff Aug 19 '16 at 21:24

score 1 · Answer 2 · answered Aug 19 '16 at 11:25

The syntax looks like this:

insert into table1
select distinct id, 'blah', 'blah', 'blah' from table2

Use single quotes for string literals, like 'blah'. (Double quotes are for delimited identifiers, e.g. if an object has an reserved word as name "table".)

How can I SQL insert a record for every id in another table?

2 Answers2