Insert data in a SQL Server table based on certain condition

Question

I have the below table with data

ID          Value                             Available
29000       Data1                               1
29000       Data2                               0
29001       Data1                               1
29002       Insert                              0
29002       Data1                               1
29003       Data2                               0

So basically I have a grouping of ID's and based on whether there is Data1 present in that grouping I have to insert another row in table with the value of Insert and whatever the Available value for Data1 is

Eventual result I want

ID          Value                             Available
29000       Data1                               1
29000       Data2                               0
29000       Insert                              1
29001       Data1                               1
29001       Insert                              1
29002       Data1                               0
29002       Insert                              0 --29002 will be skipped since it already has Insert along with Data1
29003       Data2                               0  
29003       Data3                               0  --29003 will be skipped since there was no Data1 in that grouping

Can someone please tell me how can I achieve this.

Thanks

Gordon Linoff · Accepted Answer · 2019-09-19 17:49:11Z

1

Hmmm . . . the extra rows are:

select id, 'Insert' as value, max(available) as available
from t
group by id
having sum(case when value = 'Insert' then 1 else 0 end) = 0;  -- no insert already

You can bring the data together with union all:

select id, value, available
from t
union all
select id, 'Insert' as value, max(available) as available
from t
group by id
having sum(case when value = 'Insert' then 1 else 0 end) = 0  -- no insert already
order by id;

edited Sep 19, 2019 at 17:49

answered Sep 19, 2019 at 17:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

SP1 Over a year ago

Thanks Gordon..I am getting this error Column 'available' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Gordon Linoff Over a year ago

@SP1 . . . It is surrounded by max().

SP1 Over a year ago

Sorry my bad..I was getting this error Operand data type bit is invalid for max operator and then i removed max

SP1 Over a year ago

Gordon..Thank you so much as always :) u Rock Man!!

shawnt00 Over a year ago

Do you need to limit "available" values strictly to the "Data1" rows?. In the sample data it just happens that the maximum value is always associated with those.

|

Neeraj Agarwal · Accepted Answer · 2019-09-19 18:06:46Z

0

You can try using subquery and union all:

select *
from table1
union all
select t1.ID, 'Insert', t1.Available
from table1 t1
where t1.Value = 'Data1'
and not exists
(
select 1
from table1 t2
where t2.ID = t1.ID
and t2.Value = 'Insert'
)
order by ID, Value, Available

answered Sep 19, 2019 at 18:06

Neeraj Agarwal

1,0597 silver badges5 bronze badges

Collectives™ on Stack Overflow

Insert data in a SQL Server table based on certain condition

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related