Insert if not exist

Question

Insert if not exist

girl_bj

SSCommitted

Points: 1731
More actions
July 22, 2021 at 4:12 am

#3912697

Hello. I have original table source and my working file select from another table. For example below in original table has value One and Two. How can I insert as zero amount if not exist in my working table?
Expected result as below:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

"How can I insert as zero amount if not exist in my working table?"

It seems maybe you're looking to LEFT JOIN the two tables together. Obviously, you cannot assign attributes to a row which doesn't exist. To identify that the row does "not exist" you could use LEFT JOIN and decode the NULL value (for column 'amount' in table '#working') using ISNULL.

drop table if exists #orig;
go
create table  #orig(
  ColumnA       varchar(10) not null);

insert #orig(ColumnA) values
('One'),
('Two');

drop table if exists #working;
go
create table  #working(
  ColumnA       varchar(10) not null,
  amount        int not null);  /* why float?  float is nondeterministic */
                                /* only use float if it's strictly */
                                /* necessary to store arbitrary precesion */
                                /* (which doesn't happen very often) */

insert #working(ColumnA, amount) values
('One', 100);

select o.ColumnA, isnull(w.amount, 0) as amount
from #orig o
     left join #working w on o.ColumnA=w.ColumnA;

ColumnAamount
One100
Two0

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

girl_bj SSCommitted Points: 1731 More actions · Answer 2

Thanks. Understand the concept now when to use Left Join and Not Exist.