Madhi,

Will the data copy or move from table1 to table2???

can you provide more details?

RR, it will move the data. Table1 will have no data.

yrbyogi, what do you want to know more?

Hi Madhivanan,

Are you speaking about http://msdn.microsoft.com/en-us/library/ms191160.aspx ? If yes, i am not sure to have understood the 2nd note "During an ALTER TABLE…SWITCH operation, a schema-modify lock is acquired on both the source and target tables to make sure that no other connections reference the tables during the change" and the Caution note in the Table and indexes requirements part "Partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want"

I think that , with mypoor english, i have missed something especially for the IDENTITY columns ( why these gaps and duplicate values ? )

Hello Madhivanan,

Yes, In this case , it swap the tablename ,structures and references only. Not move data actually.

Hi, Is it possible to switch data to another table on condition basis other than the entire data?

Hai madhi,

Nice tip..

Its worked for me only one time. I just create a copy of one existing table, then executed the "alter table table1 switch to table2" query. After the query execution i run the selection query of that i newly created table. The result shows exactly the same recordset of first table. then i delete all the records from the second table and run again the switching query but i i don't have get any result. I recreate the second table and run the same query, it won't be worked.

Whats the reason for that.?