umair.ashfr umair.ashfr - 3 years ago 162
SQL Question

Fewer than expected Rows INSERTED

I am using this query to return results from the said table and insert in SalesReason Table.
When i only run the select part of the statement the rows returned are > 8000 but
when i use it with insert as shown below i only get 3 rows inserted in the table.
Why and whats happening?

INSERT INTO dp_stg_sales.SalesReason (SalesReasonID, TeamName, ReasonType)
SELECT (CASE WHEN SalesReasonID_One = 'NULL' THEN 0
ELSE CAST(SalesReasonID_One AS INTEGER)
END) AS SalesReasonID,
Name_One,
ReasonType
FROM dp_stg_sales.Sales_April_Part1_Filtered


EDIT :
This the result of Select statement SelectStatementResult

Answer Source

There are many duplicate rows in the SELECT query results. It seems the dp_stg_sales.SalesReason has a unique index created with the IGNORE_DUP_KEY = ON option so only unique key values that don't already exist in the table are inserted.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download