SQL multiple INSERT OR UPDATE

Question

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 INSERTed 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

You should be able to make this into a subquery...something like insert into table case when row_count = 0 then... when row_count = 1 then... end as stuff from (select count(1) as row_count from table)a;? — user554546
– user554546, Commented Jan 11, 2017 at 17:22
Oh wait...is @@ROWCOUNT the number of rows affected by the first UPDATE? My tsql is fairly rusty. — user554546
– user554546, Commented Jan 11, 2017 at 17:25
Hmm, pardon my lack of knowledge, but will a subquery really work (with new data) before the transaction is complete? — Oak_3260548
– Oak_3260548, Commented Jan 11, 2017 at 17:25
Why not look into MERGE? msdn.microsoft.com/en-us/library/bb510625.aspx Trying to do this row by agonizing row is not a great plan. — Sean Lange
– Sean Lange, Commented Jan 11, 2017 at 17:35
Keep in mind that @@ROWCOUNT returns the number of rows affected by the LAST statement. It does not keep a running total of the number of rows affected across multiple statements. — Sean Lange
– Sean Lange, Commented Jan 11, 2017 at 17:37

Zackary Geers · Accepted Answer · 2017-01-11 17:54:45Z

4

A slightly cleaner solution to you problem may be to use:

DECLARE @Reasons TABLE ( Name NVARCHAR(50) PRIMARY KEY, ReasonType NVARCHAR(50))

INSERT INTO @Reasons ( Name, ReasonType ) VALUES  ( N'Review', N'Old Reason' )  

SELECT * FROM @Reasons

MERGE INTO @Reasons AS Target  
USING (VALUES 
('Recommendation','Other'),
('Review', 'Marketing'),
('Internet', 'Promotion')
)  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  ;

SELECT * FROM @Reasons

Then you'll get all or nothing, without all of the conditions. See https://msdn.microsoft.com/en-us/library/bb510625.aspx details on the merge statement.

edited Jan 11, 2017 at 17:54

answered Jan 11, 2017 at 17:30

Zackary Geers

3803 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Oak_3260548 Over a year ago

I don't see an UPDATE statement here...? If data exists, then they have to be updated, only if they don't a new record is created.

Zackary Geers Over a year ago

Ah, I missed that part. I'll post an updated solution.

Sean Lange Over a year ago

This is a much better answer than the accepted answer.

Oak_3260548 Over a year ago

I agree this seems to be solution covering cases where the lines may have been modified indpendently. In my case the accepted solution works fine with no limitations, but I'm sure this second solution will come handy in future too.

James Jayson · Accepted Answer · 2017-01-11 17:25:11Z

2

UPDATE TABLE .....
IF(condition)
BEGIN
/*
do insert here
*/
END
IF(condition)
BEGIN
/*
do insert here
*/
END

Source: SQL Tutorials

answered Jan 11, 2017 at 17:25

James Jayson

3361 silver badge7 bronze badges

1 Comment

Oak_3260548 Over a year ago

That looks really, really clean and simple. I will test it and mark as an asnswer when tested.

Collectives™ on Stack Overflow

SQL multiple INSERT OR UPDATE

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related