CODEWITHSUNDEEP
c#sqlsql-servercursorinsert-into
TiagoM
TiagoM

Reputation: 3526

INSERT INTO or UPDATE in case of condition

I am facing a problem at the moment, and I would like a quick solution for this.

I already found another answers about this issue but none had helped, I had read about INSERT INTO and INSERT ON DUPLICATE KEY UPDATE, but I think it doesn't really help me.

So the thing is, I have some Migrations enabled that Add new columns to existing tables, the thing is that the existing records on table prior to Migration get records with empty values even on existing columns (because I am changing the order of the columns on the new table migrated)

I am doing a SP that it's purpose is to fill the new Table migrated, according to Inserts or Updates.

The "pseudo code" (mixing sql tables knowledge and c# syntax) would be something like this:

int i = 0;
foreach(Record item in importing_data_table)
{

     if(i < tableMigrated.Length && tableMigrated[i].Column IS NULL)
     {
          UPDATE tableMigrated[i] SET Column = item.Column
     }
     else
     {
          INSERT INTO item 
     }

     i++;
}

NOTE: Deleting all the rows from the tableMigrated before inserting is not possible, because there are foreign keys to that table. I got the following error trying that approach: - The DELETE statement conflicted with the REFERENCE constraint "FK_blabla.Testing_testingTest.tableMigrated_Id".

NOTE2: Maybe my only option is using Cursors?

Thank you so much for your help in advance!

Upvotes: 1

Views: 1235

Answers (1)

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

In your Stored Procedure, use the Merge Statement. Merge provides the functionality to INSERT based on your insert condition, otherwise UPDATE. Here is an example

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)

Take a look at Technet - Inserting, Updating, and Deleting Data by Using MERGE

Upvotes: 1

Related Questions