SQL Server Conditional Insert

Question

I have a table that contains information about suppliers for a part

Background: This table is a join of

• The part master record, which contains
  • the Part Number, Part Name and default supplier.
• The other table is the Supplier information table which contains
  • The records for the part number against the supplier and their quote for the part (There is extra information I have left out of this example)

In this table some of the parts the default supplier is NULL. For those records I would like to INSERT a new record as a placeholder i.e. #3 below

Some parts have a default supplier, but there is no record of a possible supplier (with quote). For those records I would also like to INSERT a new record as a placeholder. i.e. #1 below

Current Table

+-------+-----------+-------------------+-------------------+------------+------------+
|PART   |PART NAME  |Default Supplier   |Possible Suppliers |Quote       |InfoComplete|
+-------+-----------+-------------------+-------------------+------------+------------+
|#1     |Part 1     |Supplier 5         |Supplier 1         |25.0        |0           |
|#1     |Part 1     |Supplier 5         |Supplier 2         |20.5        |0           |
|#2     |Part 2     |Supplier 10        |Supplier 10        |10.4        |1           |
|#3     |Part 3     |NULL               |Supplier 3         |9.5         |0           |
|#3     |Part 3     |NULL               |Supplier 4         |11.5        |0           |
+-------+-----------+-------------------+-------------------+------------+------------+

Desired Output (Space shown for clarity)

+-------+-----------+-------------------+-------------------+------------+------------+
|PART   |PART NAME  |Default Supplier   |Possible Suppliers |Quote       |InfoComplete|
+-------+-----------+-------------------+-------------------+------------+------------+
|#1     |Part 1     |Supplier 5         |Supplier 1         |25.0        |0           |
|#1     |Part 1     |Supplier 5         |Supplier 2         |20.5        |0           |
|#2     |Part 2     |Supplier 10        |Supplier 10        |10.4        |1           |
|#3     |Part 3     |NULL               |Supplier 3         |9.5         |0           |
|#3     |Part 3     |NULL               |Supplier 4         |11.5        |0           |
|       |           |                   |                   |            |            |     
|#1     |Part 1     |Supplier 5         |**MISSING**        |NA          |0           |
|#3     |Part 3     |**MISSING**        |**MISSING**        |NA          |0           |
+-------+-----------+-------------------+-------------------+------------+------------+

From what I have read a merge statement might be a solution, but I couldn't get it to work at all.

EDIT:

Sorry I should have been a little clearer in my initial post, the information is being exported to for users to review missing data. No plans to replace the NULL values in the database.

I only wanted to manipulate the data to make it clearer for users to understand the data.

Based on feedback I'm looking at better ways to display the information to the users.

Answer 1

Here is an somewhat simplified example how you can use MERGE to achieve your desired output. Only one table PartsSuppliersInfo having data about parts and suppliers like in the example above.

SQLFiddle DEMO

;WITH trgtCTE AS 
(
    SELECT Part, PartName, DefaultSupplier, PossibleSuppliers, Quote, InfoComplete FROM PartsSuppliersInfo
    WHERE DefaultSupplier = '**MISSING**' OR [PossibleSuppliers] = '**MISSING**'
)
MERGE trgtCTE AS trgt
USING
(
    SELECT DISTINCT Part, PartName, '**MISSING**' AS DefaultSupplier,'**MISSING**' AS PossibleSuppliers, NULL AS Quote, 0 AS InfoComplete  FROM PartsSuppliersInfo
    WHERE DefaultSupplier IS NULL
    UNION 
    SELECT DISTINCT p.Part, PartName, DefaultSupplier,'**MISSING**' AS PossibleSuppliers, NULL AS Quote, 0 AS InfoComplete FROM PartsSuppliersInfo p
    WHERE DefaultSupplier IS NOT NULL AND InfoComplete = 0  
) src
ON src.part = trgt.part AND src.DefaultSupplier = trgt.DefaultSupplier AND src.PossibleSuppliers = trgt.PossibleSuppliers
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Part, PartName, DefaultSupplier, PossibleSuppliers, Quote, InfoComplete)
    VALUES (Part, PartName, DefaultSupplier, PossibleSuppliers, Quote, InfoComplete)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Target part of MERGE is CTE with only rows having **MISSING** keyword. First time it's empty, but every next time statement is run, it will have the results from previous run.

Source part is sub-query with logic how to calculate which **MISSING** rows should exist.

Last two parts delete rows no longer needed (if part is no longer missing), and insert new rows.

Answer 2

In this scenario SupplierId used as PRIMARY KEY for table dbo.Supplier and Id PRIMARY KEY for table dbo.SupplierInfo which associating with table dbo.Supplier on FOREIGN KEY DefSupplierId

DECLARE @SupplierId TABLE(SupplierId int)

INSERT dbo.Supplier(Part, PartName, DefaultSupplier)
OUTPUT inserted.SupplierId INTO @SupplierId(SupplierId) 
SELECT DISTINCT s.Part, s.PartName, ISNULL(s.DefaultSupplier, '**MISSING**')       
FROM dbo.Supplier s LEFT JOIN dbo.SupplierInfo i ON s.SupplierId = i.DefSupplierId
WHERE s.DefaultSupplier IS NULL OR i.PossibleSuppliers IS NULL

INSERT dbo.SupplierInfo
SELECT SupplierId, '**MISSING**', 0, 0
FROm @SupplierId

Demo on SQLFisddle

Answer 3

I decided on not waiting to hear out your reasoning for wanting to replace Nulls with magic strings. I can only assume you want to do this because you are wanting your queries to be simple and do output the phrase **MISSING** whenever the default supplier is NULL. There are better ways to accomplish that task in a non-repetitive manner than turning your foreign keys to supplier (assuming you even have one) into a nullable varchar field that never contains a null.

I would suggest you use a view to access this data if this is the case like so:

IF ( OBJECT_ID('dbo.vw_PartsSuppliers') IS NOT NULL ) 
   DROP VIEW dbo.vw_PartsSuppliers
GO

CREATE VIEW dbo.vw_PartsSuppliers
AS
  SELECT 
         p.PartId,
         p.PartName,
         ISNULL(s.SupplierName, '**MISSING**'),
         ISNULL(s.Quote, 'NA'),
         s.InfoComplete
  FROM
         dbo.Part p
         LEFT JOIN dbo.Supplier s ON p.DefaultSupplierId = s.SupplierId            
GO  

This approach is flexible and reusable and will give you the formatting you are after. Furthermore, once a query comes along where you need to identify parts without default suppliers (which you probably will), you won't suffer a performance hit because you aren't searching for the **MISSING** magic string, but instead NULL.

Answer 4

Consider using TRIGGERS

CREATE TRIGGER NULL_DEFAULTSUPP
AFTER INSERT ON TABLE-NAME
FOR EACH ROW BEGIN
WHERE NEW.Default Supplier IS NULL
BEGIN
INSERT INTO TABLE-NAME VALUES(NEW.PART, NEW.PART-NAME, "**MISSING**", ....);
END;