How to use both 'insert into' and 'values' in one statement

Question

I'd like to join 2 tables together. TableB has 15 distinct values that I want to go into the TableA (the one I'm inserting into). However, I also want to insert individual values for TableA. For example, I want to insert the 15 individual values from TableB into TableA, but I also want to be able to insert another field ('region') in TableA

 --so far I have this
 insert into TableA ((id)
      select distinct(id) from TableB
      group by id), region values('NYC')

I'm not sure how to insert the region in there...the above fails. I need to hardcore the regions in there because they are not in the other table.

Chad · Accepted Answer · 2012-04-27 20:27:54Z

3

You probably want something like this:

insert into TableA (id, region)
select distinct id, 'NYC'
from TableB

answered Apr 27, 2012 at 20:27

Chad

7,5772 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JNK · Accepted Answer · 2012-04-27 20:27:40Z

0

INSERT INTO TableA (ID, REGION)
SELECT ID, 'NYC'
FROM TableB
GROUP BY ID

That's it.

The DISTINCT is redundant.

answered Apr 27, 2012 at 20:27

JNK

65.5k15 gold badges127 silver badges138 bronze badges

3 Comments

Chad Over a year ago

DISTINCT isn't necessarily redundant, e.g. if id is not a primary key.

JNK Over a year ago

The GROUP BY does indeed make it 100% redundant. I'd be intrigued to see duplicate id values when you group by id...

Chad Over a year ago

Ah, you're right there. I just went the other way and took out the GROUP BY

Firoz Ansari · Accepted Answer · 2012-04-27 20:27:44Z

0

Try something like this:

insert into TableA (id, region)
    select distinct id, "NYC"
    from TableB
    group by id

answered Apr 27, 2012 at 20:27

Firoz Ansari

2,5252 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to use both 'insert into' and 'values' in one statement

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related