Insert if not exist
-
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:
-
"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
Two0Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
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