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?

    Capture

    Expected result as below:

    Capture

  • "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;
    ColumnA	amount
    One 100
    Two 0

    Aus 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