CODEWITHSUNDEEP
sql-server
Álvaro García
Álvaro García

Reputation: 19356

Can I use "go" in a while inside a transaction?

I would like to change the IDs of some rows in one table and I am trying this:

begin tran
    WHILE @iteradorAccion < @FilasTotalesAcciones
    BEGIN
         --some code
         update MyTable 
         set MyField = @Fieldvalue + '_old'

         insert into MyTable(MyField) VALUES(@FieldValue)
    END
rollback

how I am a unique constraint that is not allowed two rows with the same value in MyField, first I try to rename the actual row and later I try to insert the new row.

The problem is that I get an error of unique constraint. I think that is because the update of the value is not still go to the database, so I an trying to use "go" between the update and the insert, but the code is not correct.

I can use the "go" inside a while loop? or I need first use a loop to rename and a second loop to insert the new rows?

Thank so much.

Upvotes: 0

Views: 106

Answers (2)

shA.t
shA.t

Reputation: 16958

Use COMMIT() :

COMMIT TRANSACTION (Transact-SQL)
Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]
[ ; ]

transaction_name
Is ignored by the SQL Server Database Engine. transaction_name specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but cannot exceed 32 characters. transaction_name can be used as a readability aid by indicating to programmers which nested BEGIN TRANSACTION the COMMIT TRANSACTION is associated with.

@ tran_name_variable
Is the name of a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type. If more than 32 characters are passed to the variable, only 32 characters will be used; the remaining characters are truncated.

Upvotes: 1

Ian Kenney
Ian Kenney

Reputation: 6426

perhaps you need to change 

  update MyTable 
         set MyField = @Fieldvalue + '_old'

to 

  update MyTable 
         set MyField = @Fieldvalue + '_old' 
         where MyField = @Fieldvalue

Upvotes: 2

Related Questions