December 30, 2008 at 9:43 pm
[font="Verdana"]
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
[/font]
December 31, 2008 at 6:04 am
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
January 1, 2009 at 5:27 pm
[font="Verdana"]
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
[/font]
January 2, 2009 at 5:58 am
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
January 4, 2009 at 4:26 pm
[font="Verdana"]
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
[/font]
March 23, 2010 at 8:05 am
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
February 19, 2013 at 1:49 pm
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
February 19, 2013 at 1:57 pm
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
October 22, 2015 at 1:36 am
In SQL Server 2014 (maybe also in earlier versions) SWITCHing unpartitioned tables is easy.
Example:
(Summary: SWITCHing between T1 and T2)
--Table/Index creation
CREATE TABLE [dbo].[T1] ( [A] [INT] PRIMARY KEY);
CREATE TABLE [dbo].[T2] ( [A] [INT] PRIMARY KEY);
GO
-- Populate some example table data
DECLARE @val INT = 1;
WHILE @val < 100
BEGIN
INSERT INTO dbo.T1(A) VALUES (@val);
SELECT @val=@val+1;
END;
-- Switch the two tables
ALTER TABLE dbo.T1 SWITCH TO T2;
-- Switch again
ALTER TABLE dbo.T2 SWITCH TO T1;
Perfect for a some cases! 🙂
(Edit: I had fogotten to include the actual SWITCH code! 😀 )
October 22, 2015 at 7:37 pm
Hans Lindgren (10/22/2015)
In SQL Server 2014 (maybe also in earlier versions) SWITCHing unpartitioned tables is easy.Example:
(Summary: SWITCHing between T1 and T2)
--Table/Index creation
CREATE TABLE [dbo].[T1] ( [A] [INT] PRIMARY KEY);
CREATE TABLE [dbo].[T2] ( [A] [INT] PRIMARY KEY);
GO
-- Populate some example table data
DECLARE @val INT = 1;
WHILE @val < 100
BEGIN
INSERT INTO dbo.T1(A) VALUES (@val);
SELECT @val=@val+1;
END;
Perfect for a some cases! 🙂
Where's the "switch" part of that code? Did you accidently delete it?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2015 at 4:08 pm
Hi,
Yes, I had accedentally left out the best part (the SWITCH code) :/
I have now edited my post...
Regards,
Hans
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply