Update while copy records

sql, sql-server, sql-server-2008

Solution

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.

Problem

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?

Original source