SQL insert into and select query

[RosieGp]

I need a query which writes to a table (if data is missing) and then based on the logic return a value (TRUE/FALSE) if the procedure needs to be executed.
I have following (not functional) query:

Declare @MissingData varchar(6) = 'TRUE'
Insert INTO MissingInfoTable
	(CustomerID, NoOfEntries, CustomerName, PhoneNumber, MissingDate, DateEntered, EnteredBy)   
SELECT CustomerID, NoOfEntries, CustomerName, PhoneNumber,
(SELECT  @MissingData =
            (CASE
		when COUNT(NoOfEntries) > 1 THEN 'Found duplicate data' and @MissingData = 'TRUE'
		when COUNT(NoOfEntries) < 1 THEN 'Not a customer' and @MissingData = 'FALSE'
                when PhoneNumber IS NULL THEN 'Missing Phone number' and @MissingData = 'TRUE'
		when COUNT(NoOfEntries) = 1 THEN @MissingData = 'FALSE'
		ELSE ''
	    END) as ValueString
from #CustomerInfo) as valueString , getdate(), 'Employee-Amanda'
from #CustomerInfo

so what i'm trying to do is if:

CASE 1. @MissingData = 'TRUE' and ValueString = 'Found duplicate data' Insert (CustomerID, NoOfEntries, CustomerName, PhoneNumber, MissingDate, DateEntered, EnteredBy) into MissingInfoTable and return value for MissingData = 'True'
CASE 2. @MissingData = 'FALSE', then do not insert anything into MissingInfoTable just return the value for MissingData = 'FALSE'
CASE 3. @MissingData = 'TRUE' and ValueString ='Missing Phone number' Insert (CustomerID, NoOfEntries, CustomerName, PhoneNumber, MissingDate, DateEntered, EnteredBy) into MissingInfoTable and return value for MissingData = 'True'
CASE 4. @MissingData = 'FALSE', then do not insert anything into MissingInfoTable just return the value for MissingData = 'FALSE'

Thanks for your help in advance. Thanks.

 

[TamarGranor]

What seems to missing here is how you determine whether there are missing records. Are you looking for them for a particular CustomerID? Something else?

You're also computing a variable value, @MissingData, but referring to in the calculation.

Show us an example of the raw data and the result you want.

Tamar

 

[gk53]

What are you trying to do? it is not clear from your code... you can try to use merge statement and match and not match conditions, but you must have unique values in your select

 

[RosieGp]

I'm sorry the entire logic needs to change based on different data input. SO i have to write a different query
Input data : tempTable Table1

ProductID    ProductNumber     ProductType     PlacingOrder
13           12B               ProduceApple    1
13           16D               Deli            3
13           19C               ProduceOrange   1
13           21A               Bakery          2
13           15E               Ham             4

so now i need to create a query and my expected output should be following:
Output data : Insert into tableName OutputTable1

ProuctID     ProductNumber      PlacingOrder    ProductType                              ProductDescription               ProductOrderDate
13           12B, 19C               1           ProduceApple, ProuceOrange               Multiple PRODUCE orders          getdate()
13           12B, 19C, 16D          1,3         ProduceApple, ProuceOrange, Deli         PRODUCE and DELI cannot co-exist getdate()

I started and got so far but I'm stuck how to make this query work cause i have to include ProductNumber and ProductType in my Group By clause:

Insert INTO OutputTable1
      (ProuctID
      ,ProductNumber
      ,PlacingOrder
      ,ProductType
      ,ProductDescription
      ,ProductOrderDate)   
SELECT ProductID
	  ,ProductNumber
          ,PlacingOrder
          ,ProductType
	  ,'Multiple PRODUCE orders' as ProductDescription (so here for the following input there are 2 inserts to output)
	  ,getdate() as Today_Date
from #Table1
Group by ProductID
	  ,PlacingOrder
HAVING
      COUNT(*) > 1

Any help is appreciated. thanks.