Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mangalau
Starting Member
11 Posts |
 Posted - 2008-05-16 : 03:46:47
|
| I have a large table and i wanna delete it but with where constraints is there a way , that i can do that without filling the log file , it's frustrating to truncate every time that log |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-05-16 : 04:18:05
|
you don't want to do that because the reason for the constraint is so you don't get orphaned rows...if you really want to do that... remove the constraint completely so you don't have to remove and recreate all the time you want to truncatequote:
Originally posted by mangalau
I have a large table and i wanna delete it but with where constraints is there a way , that i can do that without filling the log file , it's frustrating to truncate every time that log
--------------------keeping it simple... |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-05-16 : 08:05:39
|
quote:
Originally posted by jen
you don't want to do that because the reason for the constraint is so you don't get orphaned rows...if you really want to do that... remove the constraint completely so you don't have to remove and recreate all the time you want to truncatequote:
Originally posted by mangalau
I have a large table and i wanna delete it but with where constraints is there a way , that i can do that without filling the log file , it's frustrating to truncate every time that log
--------------------keeping it simple...
...actually Jen, i don't think that's what the OP meant? I think what they're saying is that they want to do...delete from table1where col = 'x'...but don't want it to fill the transaction log? i.e. they would use 'truncate table' except that they need to a 'where' clausemangalau - is that what you mean?Em |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-05-16 : 09:34:08
|
I think that's what he means...another option is to just set the database to Simple recovery modelquote:
Originally posted by elancaster
quote:
Originally posted by jen
you don't want to do that because the reason for the constraint is so you don't get orphaned rows...if you really want to do that... remove the constraint completely so you don't have to remove and recreate all the time you want to truncatequote:
Originally posted by mangalau
I have a large table and i wanna delete it but with where constraints is there a way , that i can do that without filling the log file , it's frustrating to truncate every time that log
--------------------keeping it simple...
...actually Jen, i don't think that's what the OP meant? I think what they're saying is that they want to do...delete from table1where col = 'x'...but don't want it to fill the transaction log? i.e. they would use 'truncate table' except that they need to a 'where' clausemangalau - is that what you mean?Em
--------------------keeping it simple... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-05-16 : 13:16:55
|
you can still use the SET ROWCOUNT command in 2005. So, that is one way to do it in batches. Or you can use the ROW_NUMBER() function to restric records. Here is one way using a loop to delete chunks of 10,000 at a time:DECLARE @RowCountSET @RowCount = 1 --Prime the loopWHILE @RowCount > 0BEGIN SET ROWCOUNT 10000 DELETE MyTable WHERE MyCol = 'foo' SET @RowCount = @@ROWCOUNTEND |
|
|
|
|
|
|
|
|
Delete sintax with where