CODEWITHSUNDEEP
sqlsql-serverinsert
Nia
Nia

Reputation: 3

SQL UPDATE or INSERT

I have tried the different solutions that found on this site when it comes to update or insert using SQL code, I got the update working but can't implement a working insert if the rows doesn't exists, the (update) code that is working looks like this, any help on how to make it insert also would be nice since I'm an *SQL Newbie, well here is the working code:

select top 0 * into #tmp_x from TTT;
select * from #tmp_x;
BULK INSERT #tmp_x FROM 'path\to\file1.csv' WITH (FIELDTERMINATOR =';',ROWTERMINATOR ='\n' ,FIRSTROW = 2);
UPDATE TTT
SET TTT.[Artikelbenämning] = #tmp_x.[Artikelbenämning],TTT.[Count of Ordernummer] = #tmp_x.[Count of Ordernummer],TTT.[Sum of Antal beställda] = #tmp_x.[Sum of Antal beställda],TTT.[Min of Skapat Datum] = #tmp_x.[Min of Skapat Datum],TTT.[Max of Planerat Plockdatum] = #tmp_x.[Max of Planerat Plockdatum],TTT.[Kommentar Inköpsplanerare] = #tmp_x.[Kommentar Inköpsplanerare]
FROM #tmp_x
WHERE TTT.Artikelnummer = #tmp_x.Artikelnummer;
drop table #tmp_x;
print 'dropped table';

Upvotes: 0

Views: 181

Answers (3)

Edmond Quinton
Edmond Quinton

Reputation: 1739

If you have SQL Server 2008 or later, you can make use of the MERGE statement. The following Microsoft TechNet article describes the merge statement pretty well: https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Your query should look something like this:

    BULK INSERT #tmp_x FROM 'path\to\file1.csv' WITH (FIELDTERMINATOR =';',ROWTERMINATOR ='\n' ,FIRSTROW = 2);

    MERGE TTT AS T
    USING #tmp_x AS S
    ON (T.Artikelnummer = S.Artikelnummer) 
    WHEN NOT MATCHED BY TARGET 
        THEN INSERT(
                     [Artikelnummer]
                    ,[Artikelbenämning]
                    ,[Count of Ordernummer]
                    ,[Sum of Antal beställda]
                    ,[Min of Skapat Datum]
                    ,[Max of Planerat Plockdatum]
                    ,[Kommentar Inköpsplanerare]
                    ) 
            VALUES( 
                     S.[Artikelnummer]
                    ,S.[Artikelbenämning]
                    ,S.[Count of Ordernummer]
                    ,S.[Sum of Antal beställda]
                    ,S.[Min of Skapat Datum]
                    ,S.[Max of Planerat Plockdatum]
                    ,S.[Kommentar Inköpsplanerare]
                  )
    WHEN MATCHED 
        THEN UPDATE 
            SET      T.[Artikelbenämning] = S.[Artikelbenämning]
                    ,T.[Count of Ordernummer] = S.[Count of Ordernummer]
                    ,T.[Sum of Antal beställda] =   S.[Sum of Antal beställda]
                    ,T.[Min of Skapat Datum] =  S.[Min of Skapat Datum]
                    ,T.[Max of Planerat Plockdatum] = S.[Max of Planerat Plockdatum]
                    ,T.[Kommentar Inköpsplanerare] =    S.[Kommentar Inköpsplanerare];

                ,T.[Kommentar Inköpsplanerare] =    ISNULL(S.[Kommentar Inköpsplanerare], T.[Kommentar Inköpsplanerare]);

-- Example exludes records where [Kommentar Inköpsplanerare] IS NULL from the merge
MERGE TTT AS T
USING (
        SELECT  
                 [Artikelnummer]
                ,[Artikelbenämning]
                ,[Count of Ordernummer]
                ,[Sum of Antal beställda]
                ,[Min of Skapat Datum]
                ,[Max of Planerat Plockdatum]
                ,[Kommentar Inköpsplanerare]
        FROM     #tmp_x 
        WHERE   [Kommentar Inköpsplanerare] IS NOT NULL
      )AS S
ON (T.Artikelnummer = S.Artikelnummer) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(
                 [Artikelnummer]
                ,[Artikelbenämning]
                ,[Count of Ordernummer]
                ,[Sum of Antal beställda]
                ,[Min of Skapat Datum]
                ,[Max of Planerat Plockdatum]
                ,[Kommentar Inköpsplanerare]
                ) 
        VALUES( 
                 S.[Artikelnummer]
                ,S.[Artikelbenämning]
                ,S.[Count of Ordernummer]
                ,S.[Sum of Antal beställda]
                ,S.[Min of Skapat Datum]
                ,S.[Max of Planerat Plockdatum]
                ,S.[Kommentar Inköpsplanerare]
              )
WHEN MATCHED 
    THEN UPDATE 
        SET      T.[Artikelbenämning] = S.[Artikelbenämning]
                ,T.[Count of Ordernummer] = S.[Count of Ordernummer]
                ,T.[Sum of Antal beställda] =   S.[Sum of Antal beställda]
                ,T.[Min of Skapat Datum] =  S.[Min of Skapat Datum]
                ,T.[Max of Planerat Plockdatum] = S.[Max of Planerat Plockdatum]
                ,T.[Kommentar Inköpsplanerare] =    S.[Kommentar Inköpsplanerare];


-- Example ignores updates on the [Kommentar Inköpsplanerare] column if the [Kommentar Inköpsplanerare] IS NULL in the source dataset
MERGE TTT AS T
USING #tmp_x AS S
ON (T.Artikelnummer = S.Artikelnummer) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(
                 [Artikelnummer]
                ,[Artikelbenämning]
                ,[Count of Ordernummer]
                ,[Sum of Antal beställda]
                ,[Min of Skapat Datum]
                ,[Max of Planerat Plockdatum]
                ,[Kommentar Inköpsplanerare]
                ) 
        VALUES( 
                 S.[Artikelnummer]
                ,S.[Artikelbenämning]
                ,S.[Count of Ordernummer]
                ,S.[Sum of Antal beställda]
                ,S.[Min of Skapat Datum]
                ,S.[Max of Planerat Plockdatum]
                ,S.[Kommentar Inköpsplanerare]
              )
WHEN MATCHED 
    THEN UPDATE 
        SET      T.[Artikelbenämning] = S.[Artikelbenämning]
                ,T.[Count of Ordernummer] = S.[Count of Ordernummer]
                ,T.[Sum of Antal beställda] =   S.[Sum of Antal beställda]
                ,T.[Min of Skapat Datum] =  S.[Min of Skapat Datum]
                ,T.[Max of Planerat Plockdatum] = S.[Max of Planerat Plockdatum]
                ,T.[Kommentar Inköpsplanerare] =    ISNULL(S.[Kommentar Inköpsplanerare], T.[Kommentar Inköpsplanerare]);

Upvotes: 3

Raul
Raul

Reputation: 3131

An alternative to @Edmond Quinton would be to delete matching rows and insert after:

DELETE TTT
FROM TTT
INNER JOIN #tmp_x
ON TTT.Artikelnummer = #tmp_x.Artikelnummer

INSERT INTO TTT
SELECT * FROM #tmp_x

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

In Sql server, you can do both insert and update in the same statement, called MERGE

MERGE TTT AS target
USING #tmp_x AS source 
ON (target.Artikelnummer = source.Artikelnummer )
WHEN MATCHED THEN 
    UPDATE SET [Artikelbenämning] = source.[Artikelbenämning],
               [Count of Ordernummer] = source.[Count of Ordernummer],
               [Sum of Antal beställda] = source.[Sum of Antal beställda]....
WHEN NOT MATCHED THEN
INSERT (<target's columns list>) -- I got a little lazy here...
VALUES (<source's columns list>)

Upvotes: 1

Related Questions