July 22, 2021 at 4:12 am
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:
July 22, 2021 at 10:22 am
"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
July 23, 2021 at 1:06 am
Thanks. Understand the concept now when to use Left Join and Not Exist.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy