SQL Stored procedure Insert Update TOP 200

1

Hi There,

I need to create a sql job out of stored procedure to insert and update in one. Picking up 200 records inserting it and updating those records status.

I have something like below. Till

Insert Into Table
(
Value1
,Value2
,...
)
SELECT TOP 200
Value1
,Value2
FROM
Where status IS NULL

-- Till above it is fine the question is below on how to update the status for the inserted records above. So, in next run it picks up TOP 200 again with status as NULL and is unique

Update
set status = 'Inserted'
WHERE

Question: How to update the status for only the row that are inserted ?

Please advise

2

Does your table has a primary key?
Please post your table definition , so that we have a more clear image.

With what you supplied , one way to do this is to use the OUTPUT

INSERT INTO ....
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
SELECT ...

UPDATE x
set status = 'Inserted'
FROM 
	yourTable AS X
	INNER JOIN @OutputTbl AS O
		ON X.ID = O.ID
WHERE
...

or

add an ORDER BY so that your SELECT TOP(200) will be always deterministic
ORDER BY ...

I would use the first one , with OUTPUT clause

3

I think a Transaction is needed (so that either both-happen or none-happen, but definitely not "first happened and second failed" :frowning: )

Also, maybe?, some locks to prevent any other changes to the Selected rows until they have been Updated?

not my forte but I would guess

INSERT INTO ....
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
SELECT ...
FROM ... TheTableToBeUpdated WITH (UPDLOCK, SERIALIZABLE)
...
4

About transaction, yes , definitely should be wrapped in a transaction.
(It is why is always good to have a code review before action).

For additional hint ... more info we need about the system

5

Hi Guys,

Thanks for Responses.

I need to updated the id in the select list and not the inserted id.

Thanks,
Parth

6

Understood. That's what @Stepson solution will do.