Problem with ALTER TABLE ... SWITCH

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Problem with ALTER TABLE ... SWITCH

Problem with ALTER TABLE ... SWITCH

Rate Topic

Display Mode

Topic Options

Author

Message

Hooman Shamsborhan

Posted Tuesday, December 30, 2008 9:43 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 28, 2012 2:52 AM
Points: 12, Visits: 48

Hi Guys

I have a unpartitioned table named Transaction_Summary and created a partitioned one called Transaction_Summary_2 and I want to transfer data from Transaction_Summary to Transaction_Summary_2 using ALTER TABLE .... SWITCH but when I run the following query I get an error:

ALTER TABLE Transaction_Summary
SWITCH TO Transaction_Summary_2

The error is:

Msg 4911, Level 16, State 2, Line 1
Cannot specify a partitioned table without partition number in ALTER TABLE SWITCH statement. The table
'PredatorASBPartitionTest.dbo.Transaction_Summary_2' is partitioned.

I should mention the partition function and schema and created tablke:

CREATE PARTITION FUNCTION TranactionSummary_Channel(nchar(4))
AS
RANGE LEFT FOR VALUES ('CARD', 'CBO', 'CHEQ', 'CUST', 'TELL')
GO

CREATE PARTITION SCHEME TranactionSummary_Schema
AS
PARTITION TranactionSummary_Channel
TO ([FG_CARD], [FG_CBO], [FG_CHEQ], [FG_CUST], [FG_TELL], [Data5])
GO

--All the fields havent been mentioned here
CREATE TABLE [dbo].[Transaction_Summary_2](
[Transaction_Serial_No] [bigint] NOT NULL,
[Channel] [nchar](4) NOT NULL,
[Card_Organisation] [nvarchar](11) NULL,
.
.
[Days_to_Expiry] [int] NULL,
[Ratio_Current_Amount_AVG_3M] [decimal](19, 2) NULL,
[Card_Function_Identifier] [nvarchar](6) NULL
) ON TranactionSummary_Schema (Channel)

Please let me know what I can do about it.

Regards,
Hooman

Post #627801

MANU-J.

Posted Wednesday, December 31, 2008 6:04 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 1,655, Visits: 8,563

I don't know much about partitioning. I tried something tht worked finally.

Step 1:

CREATE TABLE [dbo].[Transaction_Summary](
[Transaction_Serial_No] [bigint] NOT NULL,
[Channel] [nchar](4) NOT NULL,
[Card_Organisation] [nvarchar](11) NULL,
[Days_to_Expiry] [int] NULL,
[Ratio_Current_Amount_AVG_3M] [decimal](19, 2) NULL,
[Card_Function_Identifier] [nvarchar](6) NULL
) ON [FG_CHEQ]

GO
ALTER TABLE [dbo].[Transaction_Summary] WITH CHECK ADD CONSTRAINT [CK_Transaction_Summary] CHECK (([channel]='CHEQ'))
GO
ALTER TABLE [dbo].[Transaction_Summary] CHECK CONSTRAINT [CK_Transaction_Summary]

Step 2:

Inserted test data in unpartitioned table created above.

Step 3:

ALTER TABLE Transaction_Summary
SWITCH TO Transaction_Summary_2 partition 3

Finally it worked. That means you need to create your unpartitioned table on the filegroups utilised by partitioned table. Then you can switch out the data from it to valid partition.

MJ

Post #627991

Hooman Shamsborhan

Posted Thursday, January 01, 2009 5:27 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 28, 2012 2:52 AM
Points: 12, Visits: 48

Hi

Thanks for your reply. What i didnt get is that why you created the table on one the partitions onle(FG_CHEQ) what about other partitions?just because I thought we should create the table on the schema of a partition function so it covers all partitions.
And also in the ALTER TABLE ... SWITCH if we only specify one partition what happens to the rest of the data?

Regards,
Hooman

Post #628540

MANU-J.

Posted Friday, January 02, 2009 5:58 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 1,655, Visits: 8,563

From books online:

ON { | filegroup | "default" }

Specifies the partition scheme or filegroup on which the table is stored. If . If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If "default" is specified, or if ON is not specified at all, the table is stored on the default filegroup. The storage mechanism of a table as specified in CREATE TABLE cannot be subsequently altered.

If you specify a partition scheme then table will be called partitioned one.

Data will be switched out only for the partition that you will specify under Alter .... switch statement rest will remain there at source.

MJ

Post #628741

Hooman Shamsborhan

Posted Sunday, January 04, 2009 4:26 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 28, 2012 2:52 AM
Points: 12, Visits: 48

Hi

Thanks for the reply.
If you mean:
ALTER TABLE Transaction_Summary
SWITCH TO Transaction_Summary_2
ON TranactionSummary_Schema

This is not the correct syntax. Also it doesnt make sense to specify a partition number because I have created a partitioned table so the data based on the partition key will get divided inti different partitions and if i specify a partition number then the transfered data wont be partitioned!!!so what will be the benefit of all this effort to partition it!!!

Regards,
Hooman

Post #629535

nate-923023

Posted Tuesday, March 23, 2010 8:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 7:49 AM
Points: 1, Visits: 158

My understanding is that the partitions are basically logical groupings of your data that allow you to easily manipulate it as needed. For example, one use of Partitioned tables is in a Sliding-Window scenario where new data is Switched in and the oldest data is Switched out based on your business rules.

Check out this article
http://technet.microsoft.com/en-us/library/ms345146(SQL.90).aspx

Post #888144

francklamandebzh

Posted Tuesday, February 19, 2013 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:05 AM
Points: 2, Visits: 21

hello,

there are several requisites to switch data in a partitioned table from an non-partitioned table (called staging/standalone table in Microsoft whitepapers).
You may read the whitepaper : http://technet.microsoft.com/en-us/library/dd578580(v=sql.100).aspx

For example :
- the partition (of the partitioned table) which will recieve datas must be empty
- if the partitioned table is partitioned on several filegroups, the staging table AND the partition (partition of the partitioned table) must be on the same filegroup.
- the 2 tables must have the same structure (same column names, same data type for each column, same foreign key, same constraints, same index (index must be aligned)....)

You may look at section "Using the ALTER TABLE SWITCH Option" page 29 line "SWITCH Requirements in the whitepaper"

You may look at the tutorial : http://www.mssqltips.com/sqlservertip/1406/switching-data-in-and-out-of-a-sql-server-2005-data-partition/
But this tutorial does not mention index subject for the switch

I an french, so do not blame me for my English level :)

Franck

Post #1421838

francklamandebzh

Posted Tuesday, February 19, 2013 1:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:05 AM
Points: 2, Visits: 21

Hello one more time :)

i forget to mention that you may create your staging table with a Wizard.
The wizard will create ,for you, a staging table with all requisites (columns, data type, constraints, index...) to switch datas

Page 32 - section "Using the Manage Partition Wizard to Perform SWITCH Operations" of the whitepaper

Post #1421840

« Prev Topic | Next Topic »

Permissions