Geek Answers Handbook

Update while copying

I want to copy records from one table to another. By doing this, I want to set the flag of these entries that I copy.

Here's how I 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?

+4
source share
3 answers

Using OUTPUT, you can collapse it to one operatorUPDATE

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. It should work fine.

+4
source

The problem with your request is that you can get different entries in UPDATEif someone inserts some data during the execution of your request. You INSERTEDcan use the keyword with <

.

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)
+1

I think you can use a temporary table in which you will store the top 100 identifiers from your original table after you ordered them. Thus, you will avoid executing the select statement in the where update section for each identifier.

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
0
source

More articles:


All Articles