I was saving a set of values (1 record) to a table in this way:
UPDATE Table SET ...
IF @@ROWCOUNT = 0 INSERT INTO Table ...
This worked fine, but now I need to add more independent records at the same time, so I tried something like:
UPDATE Table SET ...
IF @@ROWCOUNT = 0 INSERT INTO Table ...
UPDATE Table SET ...
IF @@ROWCOUNT < 2 INSERT INTO Table ...
UPDATE Table SET ...
IF @@ROWCOUNT < 3 INSERT INTO Table ...
UPDATE Table SET ...
IF @@ROWCOUNT < 4 INSERT INTO Table ...
...in order to keep it in 1 transaction. However, starting from the 2nd declaration it adds rows even if the particular row is already existing - apparently @@ROWCOUNT
does not count INSERT
ed rows, or I misunderstood its concept another way...
I wonder if there is some feasible way to resolve this within SQL query. In this particular case, it should be OK to assume that all records are dependent on existence of the first, but it is not 100% reliable and I would like to know a better solution, if exists.
EDIT - solution:
This may not be possible in all applications (be aware, that particular updates are not checked separately), but in my case it apears to work well and should be also consistent with the data, because the set of updated/inserted data is integral.
UPDATE Table SET ...
UPDATE Table SET ...
UPDATE Table SET ...
UPDATE Table SET ...
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Table ...
INSERT INTO Table ...
INSERT INTO Table ...
INSERT INTO Table ...
END
UPDATE TABLE .....
IF(condition)
BEGIN
/*
do insert here
*/
END
IF(condition)
BEGIN
/*
do insert here
*/
END
Source: SQL Tutorials