Update while copy records

user_575376 0
Viewed 67

I want to copy records from one table to another. While doing this I want to set a flag of those records I copy.

This is how I would do it (simplified):

BEGIN TRANSACTION copyTran

  insert into destination_table (name)
  select top 100 name 
  from source_table WITH (TABLOCKX)
  order by id

  update source_table
  set copy_flag = 1
  where id in (select top 100 id from source_table order by id)

COMMIT TRANSACTION copyTran

Is there an easier way?

sql-server-2008 sql-server sql
3 Answers

By leveraging OUTPUT clause you can boil it down to a single UPDATE statement

UPDATE source_table
   SET copy_flag = 1
OUTPUT inserted.name
  INTO destination_table(name)
 WHERE id IN 
(
  SELECT TOP 100 id 
    FROM source_table 
   ORDER BY id
)

Note: Now tested. Should work just fine.

user_19202320

The problem with your query is, that you may get different records in your UPDATE if someone inserts some data while you are running your query. It is saver to use the INSERTED keyword.

Declare @temp TABLE (Id integer);

INSERT INTO destination_table (name) 
    OUTPUT INSERTED.Id into @temp
SELECT TOP 100 name 
FROM source_table
ORDER BY id

UPDATE source_table
SET copy_flag = 1
WHERE Id IN (SELECT Id FROM @temp)
user_29642910

I think that you could use a temporary table in which you will store the top 100 ids from your source table, after having ordered them. This way you will avoid executing the select statement in where clause of update for each id.

BEGIN TRANSACTION copyTran

  insert into destination_table (name)
  select top 100 name 
  from source_table
  order by id

  declare @Ids TABLE(id int)
  @Ids = (select top 100 id from source_table order by id)

  update source_table
  set copy_flag = 1
  where id in (SELECT * FROM @Ids)

COMMIT TRANSACTION copyTran
user_9131240
Related