Table Data delete

navuser1

I want to delete data from transaction tables through
SQL commands.Which command will be more help full, "Trancate" Or
"Delete"???

Suggest me

David_Singleton

navuser1 wrote:

I want to delete data from transaction tables through
SQL commands.Which command will be more help full, "Trancate" Or
"Delete"???

Suggest me

Try the little [X] on the top right of SQL Management Studio.

Then go to Control Panel and Uninstall SSMS from your computer.

Basically if you are thinking of doing this, then you really should not be allowed to have access to SQL.

](*,)

bbrown

If you're unclear of the difference between "DELETE FROM TABLE" and "TRUNCATE TABLE", you should not even be considering touching the Navision database directly from SQL.

kine

Navuser, really, start by learning what these commands are doing and learn all the things about NAV on SQL (mainly that it is very dangerous to touch the data outside NAV) and after that if something is not clear, we can help you.

David_Singleton

kine wrote:

Navuser, really, start by learning what these commands are doing and learn all the things about NAV on SQL (mainly that it is very dangerous to touch the data outside NAV) and after that if something is not clear, we can help you.

Now I understand what you were telling me at lunch the other day. And you you were 100% right.

idiot

Very interesting.
Why not try both & let us know the results?

garak

navuser1, please. Before posting some questions like this:
Read the f****** manuals.

The difference between the delete statement and the truncate statement is that one of this log the transaction. but you will wonder whats happens when delete datas from tables (sql) that have Sift Tables. I hope you know what this is.

The best way: format your HDD and shutdown the server ;-)

Than all transactions are deletet \:D/

bbrown

garak wrote:

navuser1, please. Before posting some questions like this:
Read the f****** manuals.

The difference between the delete statement and the truncate statement is that one of this log the transaction. but you will wonder whats happens when delete datas from tables (sql) that have Sift Tables. I hope you know what this is.

The best way: format your HDD and shutdown the server ;-)

Than all transactions are deletet \:D/

Actually, they both log the activity. Just at different levels of detail.

hawwa

Delete transaction data directly from SQL is a very dangerous act. You have to study and understand the table relationship very clearly before delete the data so that you won't corrupt the data integrity.

If only want to delete the data in the tables added by you which you are very sure on the table design, then it should be safer. If you want to delete transaction data in NAV standard transaction table, be careful. [-o<

I hope you are clear on what you are trying to do and how serious is it after reading so many replies in this topic.

garak

ok, what i mean is following.

if u use truncate, the transactions (delete) can not be recovered with RollBack, because the transactions runs implicit. And the auto_incr. option will be set at the first option. There are typically fewer locks.

But TRUNCATE TABLE con not handle Foreign Keys, participate in an indexed view and is used by transactional replication or merge replication (system intern) nad TRUNCATE TABLE cannot activate a trigger because the oper. doesn't individ. row deletions, and my last knowledge, you can not use the where clausel.

Regards

bbrown

garak wrote:

ok, what i mean is following.

if u use truncate, the transactions (delete) can not be recovered with RollBack, because the transactions runs implicit. And the auto_incr. option will be set at the first option. There are typically fewer locks.

But TRUNCATE TABLE con not handle Foreign Keys, participate in an indexed view and is used by transactional replication or merge replication (system intern) nad TRUNCATE TABLE cannot activate a trigger because the oper. doesn't individ. row deletions, and my last knowledge, you can not use the where clausel.

Regards

I agree with everything you're saying except that a TRUNCATE cannot be rolled back.

kriki

[Topic moved from Navision forum to SQL General forum]

DaveT

David Singleton wrote:

navuser1 wrote:

I want to delete data from transaction tables through
SQL commands.Which command will be more help full, "Trancate" Or
"Delete"???

Suggest me

Try the little [X] on the top right of SQL Management Studio.

Then go to Control Panel and Uninstall SSMS from your computer.

Basically if you are thinking of doing this, then you really should not be allowed to have access to SQL.

](*,)

I haven't stoped laughing at this all evening... "Pure Genius" post =D>

David_Singleton

DaveT wrote:

...
I haven't stoped laughing at this all evening... "Pure Genius" post =D>

If I was a door in the Heart Of Gold, I would say "Glad To Be Of Service".