Create a New Row for each item is a Select

Transact-SQL
1

Hi

I have a select that returns two columns (Workdate and Cost). What I need to be able to do, is update (or insert if it doesn't exist) a row into another table with the Workday and Cost columns, for each row returned in the select.

Basically the WorkDate and Cost are the monthly bills and I need to auto generate a monthly invoice into the invoice table, or update the Cost if there is already an invoice for that Workdate.

Can someone help kick me off in this with an outline approach?

Thanks

Phil

2

Something like this:

-- update already existing workdates
UPDATE i SET
	Cost = q.Cost
FROM
	InvoiceTable i
	INNER JOIN
	(
		-- your query that returns Workdate and Cost
	) AS q ON q.Workdate = i.WorkDate;

-- insert workdates that don't exist.	
INSERT INTO InvoiceTable
(WorkDate, Cost)
SELECT WorkDate, Cost FROM
	(
		-- your query that returns Workdate and Cost
	) AS q 	
WHERE NOT EXISTS
(SELECT * FROM InvoiceTable i
	WHERE i.WorkDate = q.WorkDate)

If you are on SQL 2008 or later, you can use the MERGE statement to do the updates and inserts in a single statement.

3

Thanks James. Whilst I was travelling I took a look at Fetch & Next to see if that would help. I think your option looks neater and I expect is quicker, but I got Fetch & Next working (see below).

Is there anything wrong with this option, or is it very inefficient ?

Thanks, Phil

Declare @POID int = (	SELECT [Purchase Orders].ID
						FROM 
							[Budget Lines] INNER JOIN [Purchase Orders] ON [Budget Lines].ID = [Purchase Orders].BudgetID
						Where 
							[Internal Resource Charges] = 1 and [Project Reference] = @Project
					)


Declare c Cursor For (SELECT Workdate as [Spend Date], sum(cost) as [Spend Value] 
						FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN Resources ON Plans.ID = Resources.PlanID
						Where [Project Reference] = @Project
						Group By WorkDate
					 )
Open c

Declare @SpendDate as Date
Declare @SpendValue as Money

Fetch next From c into @SpendDate, @SpendValue
While @@Fetch_Status=0 Begin
   If Exists(
				Select ID from [Project Spend] Where POID = @POID and [Spend Date] = @SpendDate
			)
	Begin
		Update [Project Spend] Set	
				[Spend Value] = @SpendValue
		Where 
				POID = @POID and [Spend Date] = @SpendDate 
	
	End Else
	Begin
		Insert Into [Project Spend]
			([Title],[Spend Value],[Status],[Spend Date],[POID],[System],[Confidence])
			Values('Invoice Charges',@SpendValue,'Open',@SpendDate,@POID,1,'Medium')
		
	End

	Fetch next From c into @SpendDate, @SpendValue
End