Truncate a Partition in Partitioned Table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Truncate a Partition in Partitioned Table

Author

Topic

chowdary
Starting Member

3 Posts

Posted - 2007-11-14 : 19:14:09

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

30421 Posts

Posted - 2007-11-14 : 21:32:20

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"

chowdary
Starting Member

3 Posts

Posted - 2007-11-14 : 22:16:02

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 22:23:39

I think you should read Books Online about ALTER PARTITION.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 22:24:05

ALTER PARTITION MERGE or similar.

E 12°55'05.25"
N 56°04'39.16"

chowdary
Starting Member

3 Posts

Posted - 2007-11-15 : 00:10:08

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

shomprabu
Starting Member

1 Post

Posted - 2008-12-31 : 01:09:09

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

vidya_pande
Starting Member

2 Posts

Posted - 2010-02-04 : 03:04:22

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

Kristen
Test

22859 Posts

Posted - 2010-02-04 : 03:14:18

"I am not able to contribute the script over here"

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 03:26:22

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

vidya_pande
Starting Member

2 Posts

Posted - 2010-02-06 : 03:20:27

http://www.sqlservercentral.com/scripts/Truncate/69506/

Try this script

Vidyadhar pandekar.
Vidya_pande@yahoo.com

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 04:26:16

Looks very useful, thanks for sharing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources