The code

ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE)

will error if someone deletes a row in the table. And since it's about document numbering, it can't repeat a number even if...

Yes it will. Inside the while it should be

set @rowcount = @@rowcount

a simple word that make the diference

I think it would be better to work like this:

The table TemoOrders would be like this

CREATE TABLE dbo.TempOrders (

OrderType varchar(1) NOT NULL,

...

Hi there

Independent of the criteria of the archive, u can use a statement like this to delete and transfer the deleted rows into another table.

DELETE TOP (10)

FROM teste

OUTPUT Deleted.* into...

Hi

Could u tell me about the generic criteria of the rows to archive? Is it about date criteria, flags criteria

And do u use rowguids or timestamps to mark your ins,upd,dels,......

Hi

U can recreate the procedure with this option "With Execute as " (See Books Online) or u can use "Execute AS" See Books Online to execute the specific statements.

In this...

Which version of excel??

Hi

U can also use the Protection Level "EncryptSensitiveWithPassword". That way u set a password for the package and it will ask you that password every time you open the package....

Hi

after "fast analyzing" the original procedure I achieved the statement. I didn't look into the other posts, so u should apply the same stuff u applied before, at least the...

since the transactions and locks does not occur in table variables, my point still stands for small tables like the one in this procedure. anyway, The cte solution works for...

Because it has to "reserve" and then destroy the structure in the temp database and it does the COMMITs.

Temp variables only get to disk if they are big, and since...

Or U could use the table hint dirty read to see if it is about transactions or not.

Hi all

Maybe a bulk insert instead of the insert solve this. Just an ideia

Hi

I remembered, u can also replace the table variable I suggested before or the way u did it in the middle of the SELECT statement with a Common Table Expression...

Hi Gila Monster

That's true. But creating a temp table, even with no data, costs more than a table variable. That's why I justified the choice that way.