Hi - Mike,
First of all, thank you for your wonderful help.
AS to your explanations, i undestud almost everything.
The only part that i did not undestud it's when you say that the create table or alter table inside, or any other DDL statement can cause Locks on system tables.
Is that so? why? In witch system tables?
About your curiosity of why am i creating this identity column inside the Stored Proc, below is my explanation:
1) I have a table, the table name is:
Bulk_provincia (it's a staging table, loaded by a bulk_insert statement)
2)
i update the table:
update bulk_provincia set VALIDO_BULK = 1
where (numprov is not null and idprov is not null and numprov <> '' and idprov <>'')
then, i make a verification of wich records will be updated or inserted (on my destination table) and update the staging table:
update bulk_provincia set operacao_bulk ='INSERT' where numprov in
(select numprov from bulk_provincia where valido_bulk=1 and numprov not in(select numprov
from provincia))
update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and
valido_bulk=1
then i delete the records that i will not import to my destination table:
delete from bulk_provincia where valido_bulk is null
At the end, i create the identity column based on a column (one of the columns of this staging table as an clustered index), so that the insert statements will be the first
to have numbers of the identity field:
declare @maxprov as int
set @maxprov=(select max(codprov)+1 from provincia)
exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')
The objective of this, is to make an insert to the destination table (the identity values will go too) and to use (in the procedure this identity fields that are not commited yet to make same comparations).
tks,
Pedro