How to add or update new value from temp table?

Question

How to update row if exist A,B,Tarih and Not exist how to insert?

My original Code:

 Insert X(A,B,C,D,E,Tarih)
select substring(dosya,2,25) as A, 
substring(dosya,27,15) as B,
substring(dosya,70,40) as C,
CONVERT(DECIMAL(17,2),substring(dosya,52,17)) as D,
case when substring(dosya,124,2)='00' then 'TL'
     when substring(dosya,124,2)='01' then 'USD'
     when substring(dosya,124,2)='02' then 'CHF'
     when substring(dosya,124,2)='03' then 'CAD'
     when substring(dosya,124,2)='04' then 'KWD'

     when substring(dosya,124,2)='05' then 'GBP'
     when substring(dosya,124,2)='06' then 'SAR'
     when substring(dosya,124,2)='07' then 'JPY'
     when substring(dosya,124,2)='08' then 'EUR'

     when substring(dosya,124,2)='09' then 'AUD'
     when substring(dosya,124,2)='10' then 'IRR'
     when substring(dosya,124,2)='11' then 'DK'
     when substring(dosya,124,2)='12' then 'SEK'
 else 'Döviz' end  as E,
@tarih as Tarih
 from #TempLog

i want to update existing value

Pseudo code :

if exist ( A,B,C in BankaEntegrasyonLog)

{

       Update Row!

}

else

{

 Insert 

X(A,B,C,D,E,Tarih)

select substring(dosya,2,25) as   . . . . .  

}

Answer 1

Something like this:

MERGE [dbo].[X] AS TARGET
USING (
    SELECT 
        SUBSTRING([dosya],2,25) AS [A],
        SUBSTRING([dosya],27,15) AS [B],
        SUBSTRING([dosya],70,40) AS [C],
        CONVERT(DECIMAL(17,2),SUBSTRING([dosya],52,17)) AS [D],
        CASE 
            WHEN SUBSTRING([dosya],124,2)='00' THEN 'TL'
            WHEN SUBSTRING([dosya],124,2)='01' THEN 'USD'
            WHEN SUBSTRING([dosya],124,2)='02' THEN 'CHF'
            WHEN SUBSTRING([dosya],124,2)='03' THEN 'CAD'
            WHEN SUBSTRING([dosya],124,2)='04' THEN 'KWD'
            WHEN SUBSTRING([dosya],124,2)='05' THEN 'GBP'
            WHEN SUBSTRING([dosya],124,2)='06' THEN 'SAR'
            WHEN SUBSTRING([dosya],124,2)='07' THEN 'JPY'
            WHEN SUBSTRING([dosya],124,2)='08' THEN 'EUR'
            WHEN SUBSTRING([dosya],124,2)='09' THEN 'AUD'
            WHEN SUBSTRING([dosya],124,2)='10' THEN 'IRR'
            WHEN SUBSTRING([dosya],124,2)='11' THEN 'DK'
            WHEN SUBSTRING([dosya],124,2)='12' THEN 'SEK'
            ELSE 'Döviz'
        END AS [E],
        @tarih AS [Tarih]
    FROM #TempLog
) AS SOURCE
    ON SOURCE.[A] = TARGET.[A]
    AND SOURCE.[B] = TARGET.[B]
    AND SOURCE.[Tarih] = TARGET.[Tarih]
WHEN NOT MATCHED THEN
    INSERT (
        [A],
        [B],
        [C],
        [D],
        [E],
        [Tarih]
    )
    VALUES (
        SOURCE.[A],
        SOURCE.[B],
        SOURCE.[C],
        SOURCE.[D],
        SOURCE.[E],
        SOURCE.[Tarih]
    )
WHEN MATCHED THEN
    UPDATE
    SET [C] = SOURCE.[C],
        [D] = SOURCE.[D],
        [E] = SOURCE.[E];

I would also consider creating a lookup table to replace that case statement. This might make future maintenance easier.

Answer 2

according to recommendations Gordon Linoff

MERGE X AS target
USING
 (
  select Id,
         substring(dosya,2,25) as A,
         substring(dosya,27,15) as B,
         substring(dosya,70,40) as C,
         CONVERT(DECIMAL(17,2),substring(dosya,52,17)) as D,
         case when substring(dosya,124,2)='00' then 'TL'
              when substring(dosya,124,2)='01' then 'USD'
              when substring(dosya,124,2)='02' then 'CHF'
              when substring(dosya,124,2)='03' then 'CAD'
              when substring(dosya,124,2)='04' then 'KWD'
              when substring(dosya,124,2)='05' then 'GBP'
              when substring(dosya,124,2)='06' then 'SAR'
              when substring(dosya,124,2)='07' then 'JPY'
              when substring(dosya,124,2)='08' then 'EUR'
              when substring(dosya,124,2)='09' then 'AUD'
              when substring(dosya,124,2)='10' then 'IRR'
              when substring(dosya,124,2)='11' then 'DK'
              when substring(dosya,124,2)='12' then 'SEK'
              else 'Döviz' end  as TutarParaBrimi, @tarih as Tarih
  from #TempLog
  ) AS source
ON target.Id = source.Id
WHEN MATCHED THEN 
  UPDATE SET A = source.A,
             B = source.B,
             C = source.C,
             D = source.D,
             E = source.E,
             Tarih = source.Tarih
WHEN NOT MATCHED THEN
  INSERT (A, B, C, D, E, Tarih)
  VALUES (source.A, source.B, source.C, source.D, source.E, source.Tarih);

If you don't have Id column then replace target.Id = source.Id on
A = source.A AND target.B = source.B AND target.C = source.C AND target.D = source.D
AND target.E = source.E AND target.Tarih = source.Tarih

Answer 3

You'll want to use the MERGE statement.

First, let's create a test table:

CREATE TABLE TestLog( SomeKey INTEGER, SomeAttribute VARCHAR(80));

And we can insert some values into it:

INSERT INTO TestLog( SomeKey, SomeAttribute ) VALUES ( 43, 'Something' );
INSERT INTO TestLog( SomeKey, SomeAttribute ) VALUES ( 55, 'Another' );
INSERT INTO TestLog( SomeKey, SomeAttribute ) VALUES ( 77, 'Demo' );

Now, let's use MERGE with a key that already exists. If we should update 'Something' to 'updated!' for SomeKey = 43:

MERGE TestLog AS TARGET
USING (SELECT 43 AS SomeKey, 'Updated!' AS SomeAttribute)
AS SOURCE( SomeKey, SomeAttribute )
ON (TARGET.SomeKey = SOURCE.SomeKey )
WHEN MATCHED THEN
   UPDATE SET SomeAttribute = Source.SomeAttribute
WHEN NOT MATCHED THEN
   INSERT (SomeKey, SomeAttribute)
   VALUES (SOURCE.SomeKey, SOURCE.SomeAttribute);

We can check that it worked:

SELECT * FROM TestLog WHERE SomeKey = 43;

and we indeed see "Updated!" for SomeAttribute. We can try a new value:

MERGE TestLog AS TARGET
USING (SELECT 22 AS SomeKey, 'Newone!' AS SomeAttribute)
AS SOURCE( SomeKey, SomeAttribute )
ON (TARGET.SomeKey = SOURCE.SomeKey )
WHEN MATCHED THEN
   UPDATE SET SomeAttribute = Source.SomeAttribute
WHEN NOT MATCHED THEN
   INSERT (SomeKey, SomeAttribute)
   VALUES (SOURCE.SomeKey, SOURCE.SomeAttribute);

And to check it:

SELECT * FROM TestLog;

We indeed see a new fourth row with (22, 'Newone!'). It shouldn't be hard to expand this example to your specific table and insert pattern. Let me know if you need more help.