Inserting Values Using a subquery

Question

I am creating a temp table and inserting values into it. It's really meant to be a table where I can map certain labels back to their desired value.

The issue is that I want to use the like operator in the values statement.

How can I do something like the following where I can take all values that meet a LIKE condition and assign it with a specific value (vbc)?

Any other better approaches are also appreciated?

insert into #new_cohort
(
chosen_path,
abr_cohort
)
values
('core', 'core'),
('pod', 'core'),
('Dynamic High - CORE', 'Core_High'),
('Dynamic High - POD','Core_High'),
((SELECT routing_path FROM [bid].[propensity_path_cutoffs] 
  WHERE routing_path like '%vbc%'
  and routing_path not like '%Dynamic High%'), 'vbc')

Use either `VALUES` or a `SELECT`. I shouldn't need to tell a 1300+ rep user you [shouldn't use images for code/errors/data](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) — HoneyBadger, Sep 14 '22 at 16:02
@Charlieface yes because there will be multiple rows that meet that like conditions — ATMA, Sep 14 '22 at 16:06
this should answer your question [How to create Temp table with SELECT \* INTO tempTable FROM CTE Query](https://stackoverflow.com/questions/11491240/how-to-create-temp-table-with-select-into-temptable-from-cte-query) `LIKE` is not an issue. You can use it in your sub-select. This question has answers how to format your sub-query — T.S., Sep 14 '22 at 16:11
In your `INSERT` you want to insert two columns, but your subquery only has *one* column; what about the value of the other column? — Thom A, Sep 14 '22 at 16:12
@Larnu There are double quotes in that subquery... so the second query is 'vbc' ((SELECT routing_path FROM [bid].[propensity_path_cutoffs] WHERE routing_path like '%vbc%' and routing_path not like '%Dynamic High%'), 'vbc') — ATMA, Sep 14 '22 at 16:18
There aren't any double quotes (`"`) in the query above, @ATMA . `routing_path` is **one** column; you want to `INSERT` two (`chosen_path` and `abr_cohort`). — Thom A, Sep 14 '22 at 17:02

score 1 · Accepted Answer · answered Sep 14 '22 at 16:26

I think you can do like this :

insert into #new_cohort (chosen_path, abr_cohort)
values
('core', 'core'),
('pod', 'core'),
('Dynamic High - CORE', 'Core_High'),
('Dynamic High - POD','Core_High');
  

insert into #new_cohort (chosen_path, abr_cohort)
SELECT routing_path, 'vbc' FROM [bid].[propensity_path_cutoffs] 
WHERE routing_path like '%vbc%'
and routing_path not like '%Dynamic High%')

Inserting Values Using a subquery

1 Answers1