Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with ALTER TABLE ... SWITCH


Problem with ALTER TABLE ... SWITCH

Author
Message
Hooman Shamsborhan
Hooman Shamsborhan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 49


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


MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 8766
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
Hooman Shamsborhan
Hooman Shamsborhan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 49


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


MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 8766
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
Hooman Shamsborhan
Hooman Shamsborhan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 49


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


nate-923023
nate-923023
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 201
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
francklamandebzh
francklamandebzh
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 204
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 Smile

Franck
francklamandebzh
francklamandebzh
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 204
Hello one more time Smile

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
Hans Lindgren
Hans Lindgren
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 366
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! Smile

(Edit: I had fogotten to include the actual SWITCH code! BigGrin )



Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44845 Visits: 39850
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! Smile


Where's the "switch" part of that code? Did you accidently delete it?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search