Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:
> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do with
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
> >I have table with several million rows, which I would like to move to
> > partition schema.
> >
> > I've found this option is present in ALTER TABLE command. However, this
> > command
> >
> > alter table TaleName move to ps_Schema ( PartitionColumnName)
> >
> > resulted in error:
> >
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near 'move'.
> >
> > I assume the syntax is wrong, but how to accomplish this task ? What is
> > the
> > correct syntax?
> >
> > Thank you very much.
> >
>
>