Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL insert into and select query

Status
Not open for further replies.

RosieGp

Programmer
Jun 23, 2009
83
US
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:
Code:
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:
Code:
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.
 
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
 
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
 
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
Code:
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
Code:
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:
Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top