|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Disk full from delete
space and it is set to Siimple Recovery. So now I need to break up my delete statement into 3 statements. Just trying to find the best way. I am deleting all the records and can't purge. I planned on doing it this way: DELETE CLIENT SELECT TOP 500000 ClientID FROM Client DELETE CLIENT SELECT TOP 500000 ClientID FROM Client DELETE CLIENT SELECT ClientID FROM Client This seems to work. Just curious if there is there a better way? Thanks, Tom Turns out that won't work as this is really 2 statement a delete and a
select. I need to do a Delete/Where to make this work. Is there a way to say delete 500000 records? Thanks, Tom Show quoteHide quote "tshad" <t***@pdsa.com> wrote in message news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >I am deleting over a 1.5 million records which is running my log out of >space and it is set to Siimple Recovery. > > So now I need to break up my delete statement into 3 statements. Just > trying to find the best way. I am deleting all the records and can't > purge. > > I planned on doing it this way: > > DELETE CLIENT > SELECT TOP 500000 ClientID FROM Client > > DELETE CLIENT > SELECT TOP 500000 ClientID FROM Client > > DELETE CLIENT > SELECT ClientID FROM Client > > This seems to work. > > Just curious if there is there a better way? > > Thanks, > > Tom > Can you issue a TRUNCATE TABLE statement?
-- Show quoteHide quote======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X "tshad" <t***@pdsa.com> wrote in message news:%232VEe4g7JHA.6004@TK2MSFTNGP02.phx.gbl... > Turns out that won't work as this is really 2 statement a delete and a > select. > > I need to do a Delete/Where to make this work. > > Is there a way to say delete 500000 records? > > Thanks, > > Tom > > "tshad" <t***@pdsa.com> wrote in message > news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>I am deleting over a 1.5 million records which is running my log out of >>space and it is set to Siimple Recovery. >> >> So now I need to break up my delete statement into 3 statements. Just >> trying to find the best way. I am deleting all the records and can't >> purge. >> >> I planned on doing it this way: >> >> DELETE CLIENT >> SELECT TOP 500000 ClientID FROM Client >> >> DELETE CLIENT >> SELECT TOP 500000 ClientID FROM Client >> >> DELETE CLIENT >> SELECT ClientID FROM Client >> >> This seems to work. >> >> Just curious if there is there a better way? >> >> Thanks, >> >> Tom >> > > No.
I have about 15 foreign key refs on it. What I did end up doing which works but only because I know where to do the break. DELETE CLIENT Where ClientID < 500000 DELETE CLIENT Where ClientID < 1000000 DELETE CLIENT But I was curious as to whether there was a way to say delete the 1st 500000 records. Thanks, Tom Show quoteHide quote "Michael Coles" <admin.AT.geocodenet.DOT.com> wrote in message news:ub2YuCh7JHA.1712@TK2MSFTNGP03.phx.gbl... > Can you issue a TRUNCATE TABLE statement? > > -- > > ======== > Michael Coles > "Pro T-SQL 2008 Programmer's Guide" > http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X > > > "tshad" <t***@pdsa.com> wrote in message > news:%232VEe4g7JHA.6004@TK2MSFTNGP02.phx.gbl... >> Turns out that won't work as this is really 2 statement a delete and a >> select. >> >> I need to do a Delete/Where to make this work. >> >> Is there a way to say delete 500000 records? >> >> Thanks, >> >> Tom >> >> "tshad" <t***@pdsa.com> wrote in message >> news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>>I am deleting over a 1.5 million records which is running my log out of >>>space and it is set to Siimple Recovery. >>> >>> So now I need to break up my delete statement into 3 statements. Just >>> trying to find the best way. I am deleting all the records and can't >>> purge. >>> >>> I planned on doing it this way: >>> >>> DELETE CLIENT >>> SELECT TOP 500000 ClientID FROM Client >>> >>> DELETE CLIENT >>> SELECT TOP 500000 ClientID FROM Client >>> >>> DELETE CLIENT >>> SELECT ClientID FROM Client >>> >>> This seems to work. >>> >>> Just curious if there is there a better way? >>> >>> Thanks, >>> >>> Tom >>> >> >> > Can you drop the foreign keys, perform the truncate, and recreate the
foreign keys? -- Show quoteHide quote======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X "tshad" <t***@pdsa.com> wrote in message news:uiit$ah7JHA.1764@TK2MSFTNGP06.phx.gbl... > No. > > I have about 15 foreign key refs on it. > > What I did end up doing which works but only because I know where to do > the break. > > DELETE CLIENT > Where ClientID < 500000 > > DELETE CLIENT > Where ClientID < 1000000 > > DELETE CLIENT > > But I was curious as to whether there was a way to say delete the 1st > 500000 records. > > Thanks, > > Tom > > "Michael Coles" <admin.AT.geocodenet.DOT.com> wrote in message > news:ub2YuCh7JHA.1712@TK2MSFTNGP03.phx.gbl... >> Can you issue a TRUNCATE TABLE statement? >> >> -- >> >> ======== >> Michael Coles >> "Pro T-SQL 2008 Programmer's Guide" >> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X >> >> >> "tshad" <t***@pdsa.com> wrote in message >> news:%232VEe4g7JHA.6004@TK2MSFTNGP02.phx.gbl... >>> Turns out that won't work as this is really 2 statement a delete and a >>> select. >>> >>> I need to do a Delete/Where to make this work. >>> >>> Is there a way to say delete 500000 records? >>> >>> Thanks, >>> >>> Tom >>> >>> "tshad" <t***@pdsa.com> wrote in message >>> news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>>>I am deleting over a 1.5 million records which is running my log out of >>>>space and it is set to Siimple Recovery. >>>> >>>> So now I need to break up my delete statement into 3 statements. Just >>>> trying to find the best way. I am deleting all the records and can't >>>> purge. >>>> >>>> I planned on doing it this way: >>>> >>>> DELETE CLIENT >>>> SELECT TOP 500000 ClientID FROM Client >>>> >>>> DELETE CLIENT >>>> SELECT TOP 500000 ClientID FROM Client >>>> >>>> DELETE CLIENT >>>> SELECT ClientID FROM Client >>>> >>>> This seems to work. >>>> >>>> Just curious if there is there a better way? >>>> >>>> Thanks, >>>> >>>> Tom >>>> >>> >>> >> > > "Michael Coles" <admin.AT.geocodenet.DOT.com> wrote in message I can and actually do that for one of the tables.news:09398995-01B5-4ABB-B175-98516414FD87@microsoft.com... > Can you drop the foreign keys, perform the truncate, and recreate the > foreign keys? The problem is that this is a script we will be using over the next 6 months and foreign keys can change and the names of the foreign keys could change if someone drops it and re-adds on the customer side. So I just disable all foreign keys (actually all constraints) before doing my inserts and then enable them all. I actually move the database to a test database first and run the scripts and fix anything that may have changed and when it works clean I run it on the actual database. Thanks, Tom Show quoteHide quote > > -- > > ======== > Michael Coles > "Pro T-SQL 2008 Programmer's Guide" > http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X > > > "tshad" <t***@pdsa.com> wrote in message > news:uiit$ah7JHA.1764@TK2MSFTNGP06.phx.gbl... >> No. >> >> I have about 15 foreign key refs on it. >> >> What I did end up doing which works but only because I know where to do >> the break. >> >> DELETE CLIENT >> Where ClientID < 500000 >> >> DELETE CLIENT >> Where ClientID < 1000000 >> >> DELETE CLIENT >> >> But I was curious as to whether there was a way to say delete the 1st >> 500000 records. >> >> Thanks, >> >> Tom >> >> "Michael Coles" <admin.AT.geocodenet.DOT.com> wrote in message >> news:ub2YuCh7JHA.1712@TK2MSFTNGP03.phx.gbl... >>> Can you issue a TRUNCATE TABLE statement? >>> >>> -- >>> >>> ======== >>> Michael Coles >>> "Pro T-SQL 2008 Programmer's Guide" >>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X >>> >>> >>> "tshad" <t***@pdsa.com> wrote in message >>> news:%232VEe4g7JHA.6004@TK2MSFTNGP02.phx.gbl... >>>> Turns out that won't work as this is really 2 statement a delete and a >>>> select. >>>> >>>> I need to do a Delete/Where to make this work. >>>> >>>> Is there a way to say delete 500000 records? >>>> >>>> Thanks, >>>> >>>> Tom >>>> >>>> "tshad" <t***@pdsa.com> wrote in message >>>> news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>>>>I am deleting over a 1.5 million records which is running my log out of >>>>>space and it is set to Siimple Recovery. >>>>> >>>>> So now I need to break up my delete statement into 3 statements. Just >>>>> trying to find the best way. I am deleting all the records and can't >>>>> purge. >>>>> >>>>> I planned on doing it this way: >>>>> >>>>> DELETE CLIENT >>>>> SELECT TOP 500000 ClientID FROM Client >>>>> >>>>> DELETE CLIENT >>>>> SELECT TOP 500000 ClientID FROM Client >>>>> >>>>> DELETE CLIENT >>>>> SELECT ClientID FROM Client >>>>> >>>>> This seems to work. >>>>> >>>>> Just curious if there is there a better way? >>>>> >>>>> Thanks, >>>>> >>>>> Tom >>>>> >>>> >>>> >>> >> >> >
Show quote
Hide quote
"tshad" <t***@pdsa.com> wrote in message If you're already dropping foreign keys and recreating them for one table, news:uVFbTfQ8JHA.4820@TK2MSFTNGP04.phx.gbl... > > "Michael Coles" <admin.AT.geocodenet.DOT.com> wrote in message > news:09398995-01B5-4ABB-B175-98516414FD87@microsoft.com... >> Can you drop the foreign keys, perform the truncate, and recreate the >> foreign keys? > > I can and actually do that for one of the tables. > > The problem is that this is a script we will be using over the next 6 > months and foreign keys can change and the names of the foreign keys could > change if someone drops it and re-adds on the customer side. So I just > disable all foreign keys (actually all constraints) before doing my > inserts and then enable them all. > > I actually move the database to a test database first and run the scripts > and fix anything that may have changed and when it works clean I run it on > the actual database. why not do it for the other? You could manually maintain separate scripts that drop and recreate the foreign keys/constraints as the database changes are applied. Or you could use the catalog views to create dynamic SQL statements to drop and recreate the constraints. Either way you'd get better performance and avoid the logging issues by truncating instead of deleting. -- ======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X Here is how you can delete on batches:
WHILE 1 = 1 BEGIN DELETE TOP (500000) FROM Client; IF @@ROWCOUNT < 500000 BREAK; END "Plamen Ratchev" <Pla***@SQLStudio.com> wrote in message I like that one and it worked great.news:QdednRkintbCdavXnZ2dnUVZ_sSdnZ2d@speakeasy.net... > Here is how you can delete on batches: > > WHILE 1 = 1 > BEGIN > > DELETE TOP (500000) > FROM Client; > > IF @@ROWCOUNT < 500000 BREAK; > > END > Thanks, Tom Show quoteHide quote
Show quote
Hide quote
"tshad" <t***@pdsa.com> wrote in message Look at the reply by Plamen although this syntax is not available on SQL news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >I am deleting over a 1.5 million records which is running my log out of >space and it is set to Siimple Recovery. > > So now I need to break up my delete statement into 3 statements. Just > trying to find the best way. I am deleting all the records and can't > purge. > > I planned on doing it this way: > > DELETE CLIENT > SELECT TOP 500000 ClientID FROM Client > > DELETE CLIENT > SELECT TOP 500000 ClientID FROM Client > > DELETE CLIENT > SELECT ClientID FROM Client > > This seems to work. > > Just curious if there is there a better way? > > Thanks, > > Tom Hi Tom 2000 I think the original syntax was supposed to be: DELETE Client FROM (SELECT TOP 500000 ClientID FROM Client ) AS t1 WHERE Client.au_id = t1.ClientID You could also set ROWCOUNT SET ROWCOUNT 500000; DELETE FROM Client; WHILE @@ROWCOUNT>0 DELETE FROM Client; SET ROWCOUNT 0; John;
Show quote
Hide quote
"John Bell" <jbellnewspo***@hotmail.com> wrote in message Which part is not availble in 2000 - the TOP statement?news:%230016A27JHA.4100@TK2MSFTNGP06.phx.gbl... > > "tshad" <t***@pdsa.com> wrote in message > news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>I am deleting over a 1.5 million records which is running my log out of >>space and it is set to Siimple Recovery. >> >> So now I need to break up my delete statement into 3 statements. Just >> trying to find the best way. I am deleting all the records and can't >> purge. >> >> I planned on doing it this way: >> >> DELETE CLIENT >> SELECT TOP 500000 ClientID FROM Client >> >> DELETE CLIENT >> SELECT TOP 500000 ClientID FROM Client >> >> DELETE CLIENT >> SELECT ClientID FROM Client >> >> This seems to work. >> >> Just curious if there is there a better way? >> >> Thanks, >> >> Tom > Hi Tom > > Look at the reply by Plamen although this syntax is not available on SQL > 2000 > Thanks, Tom Show quoteHide quote > I think the original syntax was supposed to be: > > DELETE Client > FROM (SELECT TOP 500000 ClientID FROM Client ) AS t1 > WHERE Client.au_id = t1.ClientID > > You could also set ROWCOUNT > > SET ROWCOUNT 500000; > > DELETE FROM Client; > > WHILE @@ROWCOUNT>0 > DELETE FROM Client; > SET ROWCOUNT 0; > > John; I think TOP (n) will break in 2000 (incorrect syntax), should be TOP n
(without the parens). Show quoteHide quote >> Look at the reply by Plamen although this syntax is not available on SQL >> 2000 >> > > Which part is not availble in 2000 - the TOP statement? > Correct, the DELETE statement in SQL Server 2000 does not support the > Which part is not availble in 2000 - the TOP statement? > TOP clause. Oh yeah, that too. Amazing how soon we forget. <shrug>
On 6/17/09 4:07 PM, in article JMWdnSceWoxy0aTXnZ2dnUVZ_vWdn***@speakeasy.net, "Plamen Ratchev" <Pla***@SQLStudio.com> wrote: Show quoteHide quote >> >> Which part is not availble in 2000 - the TOP statement? >> > > Correct, the DELETE statement in SQL Server 2000 does not support the > TOP clause. DELETE TOP is not available in 2000
-- Show quoteHide quote======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X "tshad" <t***@pdsa.com> wrote in message news:OQbt9s27JHA.5828@TK2MSFTNGP04.phx.gbl... > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:%230016A27JHA.4100@TK2MSFTNGP06.phx.gbl... >> >> "tshad" <t***@pdsa.com> wrote in message >> news:OZmoE0g7JHA.1380@TK2MSFTNGP05.phx.gbl... >>>I am deleting over a 1.5 million records which is running my log out of >>>space and it is set to Siimple Recovery. >>> >>> So now I need to break up my delete statement into 3 statements. Just >>> trying to find the best way. I am deleting all the records and can't >>> purge. >>> >>> I planned on doing it this way: >>> >>> DELETE CLIENT >>> SELECT TOP 500000 ClientID FROM Client >>> >>> DELETE CLIENT >>> SELECT TOP 500000 ClientID FROM Client >>> >>> DELETE CLIENT >>> SELECT ClientID FROM Client >>> >>> This seems to work. >>> >>> Just curious if there is there a better way? >>> >>> Thanks, >>> >>> Tom >> Hi Tom >> >> Look at the reply by Plamen although this syntax is not available on SQL >> 2000 >> > > Which part is not availble in 2000 - the TOP statement? > > Thanks, > > Tom > >> I think the original syntax was supposed to be: >> >> DELETE Client >> FROM (SELECT TOP 500000 ClientID FROM Client ) AS t1 >> WHERE Client.au_id = t1.ClientID >> >> You could also set ROWCOUNT >> >> SET ROWCOUNT 500000; >> >> DELETE FROM Client; >> >> WHILE @@ROWCOUNT>0 >> DELETE FROM Client; >> SET ROWCOUNT 0; >> >> John; > >  
Other interesting topics
Is it collation or something else
Database documentor Earliest SQL Version for a given feature... How to BKP? Why sa shows in Logins Determine actual row size? SQL SERVER 2K8 and 2K5 and 2K Differences with respect to CHAR typ query/join issue Fetch 1.2 million records in 30 seconds What happens to running trace when SQL is shutdown |
|||||||||||||||||||||||
