Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with ALTER TABLE ... SWITCH Expand / Collapse
Author
Message
Posted Tuesday, December 30, 2008 9:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, December 31, 2008 6:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Posted Thursday, January 1, 2009 5:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, January 2, 2009 5:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Posted Sunday, January 4, 2009 4:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, March 23, 2010 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 1, Visits: 186
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
Posted Tuesday, February 19, 2013 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 2, Visits: 143
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
Posted Tuesday, February 19, 2013 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 2, Visits: 143
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 »

Add to briefcase

Permissions Expand / Collapse