Select and Update of records without being used by other users?

[M Wells]

Hi All,

I need to change a column value in several thousand records in a table
and output a list of the record ids of the records I've updated into
another table.

The table, however, is being used by other users via a website, so I
need to make certain that website requests don't touch on the records
I'm selecting / updating.

My attempt at a stored procedure goes thus:

CREATE proc dbo.stp_mw_tag_irecords
AS
BEGIN TRANSACTION

INSERT INTO tbl_tagged (recid)
select top 100000 recid
FROM tbl_active with(UPDLOCK)
WHERE rectype='a'
and used=0

UPDATE tbl_active set used = 1 where recid in (select recid from
tbl_tagged)

COMMIT TRANSACTION

As near as I can tell, I'll end up with a table with 100000 records in
it, comprising the record id of each of the records that meet my
criteria, and the source records will now be tagged as used (ie used =
1).

I'm wondering if anyone can tell me if there's a more efficient way of
achieving this result, in terms of load on the server?

Any help much appreciated!

Much warmth,

Murray

[Saghir Taj]

Dear M. Wells

Please see online books for DELETED, INSERTED Cursors. by using insert
into .... select ....... you may copy deleted, updated, inserted
records to other table.

Example "Select * from Deleted" or "Select * from Inserted"

I hope it will help you! Best of luck!

Me,
Saghir Taj

M Wells <pl**********@planetthoughtful.org> wrote in message news:<t5********************************@4ax.com>. ..

Hi All,

I need to change a column value in several thousand records in a table
and output a list of the record ids of the records I've updated into
another table.

The table, however, is being used by other users via a website, so I
need to make certain that website requests don't touch on the records
I'm selecting / updating.

My attempt at a stored procedure goes thus:

CREATE proc dbo.stp_mw_tag_irecords
AS
BEGIN TRANSACTION

INSERT INTO tbl_tagged (recid)
select top 100000 recid
FROM tbl_active with(UPDLOCK)
WHERE rectype='a'
and used=0

UPDATE tbl_active set used = 1 where recid in (select recid from
tbl_tagged)

COMMIT TRANSACTION

As near as I can tell, I'll end up with a table with 100000 records in
it, comprising the record id of each of the records that meet my
criteria, and the source records will now be tagged as used (ie used =
1).

I'm wondering if anyone can tell me if there's a more efficient way of
achieving this result, in terms of load on the server?

Any help much appreciated!

Much warmth,

Murray

[Bruce]

Hi Murray,

It might be a little faster to temporarily create an update trigger on
the table that only does anything if it's you running it that just
inserts into the table that records which records have been updated.
That way you don't have to join against your 100000 row table.

create trigger...
if system_user = 'your system user name'
Begin
insert into ... values (inserted.recid...)
End

Then you can just update the original table as you'd like.

Bruce
M Wells <pl**********@planetthoughtful.org> wrote in message news:<t5********************************@4ax.com>. ..

Hi All,

I need to change a column value in several thousand records in a table
and output a list of the record ids of the records I've updated into
another table.

The table, however, is being used by other users via a website, so I
need to make certain that website requests don't touch on the records
I'm selecting / updating.

My attempt at a stored procedure goes thus:

CREATE proc dbo.stp_mw_tag_irecords
AS
BEGIN TRANSACTION

INSERT INTO tbl_tagged (recid)
select top 100000 recid
FROM tbl_active with(UPDLOCK)
WHERE rectype='a'
and used=0

UPDATE tbl_active set used = 1 where recid in (select recid from
tbl_tagged)

COMMIT TRANSACTION

As near as I can tell, I'll end up with a table with 100000 records in
it, comprising the record id of each of the records that meet my
criteria, and the source records will now be tagged as used (ie used =
1).

I'm wondering if anyone can tell me if there's a more efficient way of
achieving this result, in terms of load on the server?

Any help much appreciated!

Much warmth,

Murray