TiagoM TiagoM - 7 days ago 6
SQL Question

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.


  • I want to insert new records always except in the case that I found an existing record on the table with empty columns (column Id - primary key - is filled)



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!

Answer

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

Comments