June 15, 2010 at 11:18 am
How can you justify doing an upsert (update, if not exist, insert) with the use of @@rowcount. I am finding a lot of instances on the net where you get the following definition but I can't make sense of it because I do understand how row count works.
UPDATE Table1 SET (...) WHERE Column1 = " "
IF @@ROWCOUNT = 0
INSERT INTO Table1 VALUES (...)
I know @@rowcount returns the amount of rows affected, but I can't see how this could work when you are using a bunch of rows to UPSERT into a table. Sure it would work if I wanted to UPSERT only 1 record, but can this somehow be applied to a bunch of records being upserted at one time? Does SQL server take one row at a time, check the update, and if it goes through @@rowcount is set to 1, then it checks the IF statement, then starts over again with the next record? That seems to be the only way it would work for UPSERTing with an entire set of data.
Now I am using a temp table #LoadReadings to UPSERT into a physical table named Readings. The Readings table has a primary key but is just an autogenerated number so I never choose a value for it. I have the following table structure for both tables:
CREATE TABLE [Readings]
(
ReadingID int IDENTITY(1,1) PRIMARY KEY,
ReadingDate Datetime,
ReadingAreaID INT,
AnalyteID INT,
ReadingValue FLOAT,
Posted BIT
)
This has the same structure as the #LoadReadings tables.
I need to find a way to update based on the following WHERE clause:
Readings.ReadingDate = #LoadReadings.ReadingDate AND
Readings.ReadingAreaID = #LoadReadings.ReadingAreaID AND
Readings.AnalyteID = #LoadReadings.AnalyteID
Now if these conditions are met I need to update the record, but only if its Posted value is set to FALSE (If the record is already posted I leave it be and don't update or insert.) Additionaly, I need to insert the record if the constraints in the WHERE clause are not found (i.e. no record for that analyte, in that Area, on that day exists)
Ideas?
June 15, 2010 at 11:44 am
loki1049 (6/15/2010)
How can you justify doing an upsert (update, if not exist, insert) with the use of @@rowcount. I am finding a lot of instances on the net where you get the following definition but I can't make sense of it because I do understand how row count works.
UPDATE Table1 SET (...) WHERE Column1 = " "
IF @@ROWCOUNT = 0
INSERT INTO Table1 VALUES (...)
I know @@rowcount returns the amount of rows affected, but I can't see how this could work when you are using a bunch of rows to UPSERT into a table. Sure it would work if I wanted to UPSERT only 1 record, but can this somehow be applied to a bunch of records being upserted at one time? Does SQL server take one row at a time, check the update, and if it goes through @@rowcount is set to 1, then it checks the IF statement, then starts over again with the next record? That seems to be the only way it would work for UPSERTing with an entire set of data.
Your thinking is correct ... the "IF @@ROWCOUNT = 0" is good for a single record but is not set based. You could use it by looping through with a cursor, but that is not a good idea as it does not use the power of SQL Server, which is to perform operations on sets of data.
You can do what you want to in a set based fashion without looping or using @@ROWCOUNT. I'm not sure if I'm tracking 100% with what it is you're wanting to do (and there is no sample data provided), but something like the following is an option:
UPDATE Readings
SET ReadingDate = lr.ReadingDate,
ReadingAreaID = lr.ReadingAreaID,
AnalyteID = lr.AnalyteID,
ReadingValue = lr.ReadingValue,
Posted = lr.Posted
FROM Readings r
JOIN #LoadReadings lr
ON r.ReadingDate = lr.ReadingDate
AND r.ReadingAreaID = lr.ReadingAreaID
AND r.AnalyteID = lr.AnalyteID
WHERE r.Posted = 0
INSERT INTO Readings (ReadingDate,ReadingAreaID, AnalyteID, ReadingValue,Posted)
SELECT lr.ReadingDate,
lr.ReadingAreaID,
lr.AnalyteID,
lr.ReadingValue,
lr.Posted
FROM #LoadReadings lr
LEFT JOIN Readings r
ON r.ReadingDate = lr.ReadingDate
AND r.ReadingAreaID = lr.ReadingAreaID
AND r.AnalyteID = lr.AnalyteID
WHERE r.ReadingID IS NULL
Keep in mind: do the update before the insert. Otherwise it will re-update all the records that you just inserted, which would just be a waste of resources.
June 15, 2010 at 12:02 pm
Thanks, that is exactly what I was trying to do only I wanted to somehow do it in 1 table pass not 2. I thought there was a way to implement this by using the @@rowcount method, but I guess not.
Thanks again for the quick response.
June 15, 2010 at 12:10 pm
For SQL Server 2008, you can use MERGE.
Prior to SQL '08, don't think you can do it in one statement.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply