SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Truncate a Partition in Partitioned Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chowdary
Starting Member

3 Posts

Posted - 11/14/2007 :  19:14:09  Show Profile  Reply with Quote
Hi all,

We have a table with 15 Partitions in SQL Server.Can i write a stored procedure or an SQL statement just to truncate a particular partition by passing the partition name.

Any Suggestions??????????

Thanks
Chow.

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/14/2007 :  21:32:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using Enterprise Edition? Then go see ALTER PARTITION SCHEMA.
If you are using partitioned view, you must first remove the UNION ALL for the partitioned table in question and then truncate.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

chowdary
Starting Member

3 Posts

Posted - 11/14/2007 :  22:16:02  Show Profile  Reply with Quote
I am not using partitioned view. This is just a partitioned table. So can is still do with ALTER PARTITION SCHEMA? If yes means can you please give me more details........

Thanks in advance.

Chow.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/14/2007 :  22:23:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think you should read Books Online about ALTER PARTITION.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/14/2007 :  22:24:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER PARTITION MERGE or similar.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

chowdary
Starting Member

3 Posts

Posted - 11/15/2007 :  00:10:08  Show Profile  Reply with Quote
I gone through SQL Server books and it says by executing ALTER PARTITION FUNCTION, one partition of any table or index that uses the partition function can be split into two partitions, or two partitions can be merged into one less partition.But i just want truncate a particular partition so that i can reload the data...........Is there any way to do this???????

Please suggest me.


Thanks
Chow
Go to Top of Page

shomprabu
Starting Member

USA
1 Posts

Posted - 12/31/2008 :  01:09:09  Show Profile  Reply with Quote
create a dummy table which has exact schema as partitioned table. If u have index in partitioned table, do have the same in dummy table.
ALTER PARTITION <Partitioned Table Name> switch partition <partition #> to <dummy table name>
DROP table <dummy table name>



Shom
Go to Top of Page

vidya_pande
Starting Member

India
2 Posts

Posted - 02/04/2010 :  03:04:22  Show Profile  Reply with Quote
Hi Guys,

Please mail me on vidya_pande@yahoo.com, i have procedure created to automatically truncate the table partition.

I am not able to contribute the script over here.

Regards,
Vidhyadhar
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/04/2010 :  03:14:18  Show Profile  Reply with Quote
"I am not able to contribute the script over here"

Mail it to me then, I'm able to contribute the script over here.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 02/04/2010 :  03:26:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by vidya_pande

Hi Guys,

Please mail me on vidya_pande@yahoo.com, i have procedure created to automatically truncate the table partition.

I am not able to contribute the script over here.

Regards,
Vidhyadhar


Note that you can't attach a file. Instead copy the code and paste it here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vidya_pande
Starting Member

India
2 Posts

Posted - 02/06/2010 :  03:20:27  Show Profile  Reply with Quote
http://www.sqlservercentral.com/scripts/Truncate/69506/

Try this script

Vidyadhar pandekar.
Vidya_pande@yahoo.com
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/06/2010 :  04:26:16  Show Profile  Reply with Quote
Looks very useful, thanks for sharing.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000