I wanna Insert values into my DB using a plain SQL query.

I don't get an error message, just nothing gets inserted.
I even checked to see if the values were in the variables, but they were fine.

• excelDate is a Datetime
• excelDatePlusTimeSpan is a DateTime
• formattedDate is a string

object[] paramItems = new object[]
{
    new SqlParameter("@ExcelDate", excelDate),
    new SqlParameter("@ExcelDatePlusTimeSpan", excelDatePlusTimeSpan),
    new SqlParameter("@FormattedDate", formattedDate),
};

int items = db.Database.ExecuteSqlRaw(@$"
    INSERT INTO DarsMissingSale (ProductNr, MissingSystem)
        SELECT Product_NR Product,  'Dars' AS MissingSystem
        From PRODUCTSALE PS 
        JOIN VARIATION V ON PS.VARIATION_CODE = V.VARIATION_CODE
        LEFT JOIN DarsSales DS 
        ON PS.PRODUCT_NR = DS.IdentificationNumber
        WHERE V.PRODUCT_TYPE = 3 and (DS.IdentificationNumber is Null) AND (ORDER_DATE  LIKE '@FormattedDate' OR (PurchaseDate BETWEEN @ExcelDate AND @ExcelDatePlusTimeSpan)) 
", paramItems);

I don't know what I am doing wrong. I followed the documentation on this website.
Maybe there is a problem with the @ placeholders. But I don't think so.

CodePudding user response：

INSERT INTO...SELECT will silently insert zero rows if the SELECT portion returns no data. You have a few places that could cause no data in the SELECT such as the first JOIN and the WHERE clause.

Run just the SELECT portion of your SQL by itself in a SQL client/IDE, find out why that is returning zero rows, then fix it and put it back into your INSERT INTO ... SELECT code.

CodePudding user response：

It didn't work because the ' ' on the '@FormattedDate' placeholder weren't needed.

object[] paramItems = new object[]
{
    new SqlParameter("@ExcelDate", excelDate),
    new SqlParameter("@ExcelDatePlusTimeSpan", excelDatePlusTimeSpan),
    new SqlParameter("@FormattedDate", formattedDate),
};

int items = db.Database.ExecuteSqlRaw(@$"
    INSERT INTO DarsMissingSale (ProductNr, MissingSystem)
        SELECT Product_NR Product,  'Dars' AS MissingSystem
        From PRODUCTSALE PS 
        JOIN VARIATION V ON PS.VARIATION_CODE = V.VARIATION_CODE
        LEFT JOIN DarsSales DS 
        ON PS.PRODUCT_NR = DS.IdentificationNumber
        WHERE V.PRODUCT_TYPE = 3 and (DS.IdentificationNumber is Null) AND (ORDER_DATE  LIKE @FormattedDate OR (PurchaseDate BETWEEN @ExcelDate AND @ExcelDatePlusTimeSpan)) 
", paramItems);