data movement from one partition to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data movement from one partition to another

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2014-01-22 : 01:32:12

I'm moving set of data from one partition to another what is the best way.

what all the things need to be considered

Note:
the set of data will be all from one partition to another one partition

My current query:

UPDATE table1
SET table1.partitioncolumn = @newpartitioncolumn
FROM table1
INNER JOIN table2
ON table1.id = table1.id
AND table1.partitioncolumn = @oldpartitioncolumn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:28:16

see
http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shaggy
Posting Yak Master

248 Posts

Posted - 2014-01-23 : 01:22:12

Hi visakh16,
I believe partition switching is to transfer\take out all avaibale data from a partition, but i need to move only part of data from a partition.It can be 1 row from a whole of 1million rows in a single parition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 06:45:12

quote:
Originally posted by shaggy

Hi visakh16,
I believe partition switching is to transfer\take out all avaibale data from a partition, but i need to move only part of data from a partition.It can be 1 row from a whole of 1million rows in a single parition.

sorry that doesnt make sense. why would you want to do that. Unless the value changes for column based on which table is partitioned i dont thing there's any need for that.
can you elaborate on your specific scenario please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shaggy
Posting Yak Master

248 Posts

Posted - 2014-01-28 : 02:08:52

Hi visakh16,
I want to move data from one partition to another.
for ex: My patition is location specific, when the customer transfer from one location to another (loc1 to loc2) I need to move all the respective data of the customer in loc1 to loc2 so when I update the partition column in all the tables where the customer reside will move the location.There is around 500 tables involved so I would like to hear the optimal solution.

Note:
1)no architecture change coz lot of efforts involved
2)no batch process

The SP has 300 tables (it can grow later) of update partition column. currently it is timeout taking 40 sec but second run it is taking 10 sec need to lower the time by 20 sec. The SP is taking more time to generate plan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:02:45

quote:
Originally posted by shaggy

Hi visakh16,
I want to move data from one partition to another.
for ex: My patition is location specific, when the customer transfer from one location to another (loc1 to loc2) I need to move all the respective data of the customer in loc1 to loc2 so when I update the partition column in all the tables where the customer reside will move the location.There is around 500 tables involved so I would like to hear the optimal solution.

Note:
1)no architecture change coz lot of efforts involved
2)no batch process

The SP has 300 tables (it can grow later) of update partition column. currently it is timeout taking 40 sec but second run it is taking 10 sec need to lower the time by 20 sec. The SP is taking more time to generate plan.

So you want this to happen in real time or in periodical basis?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shaggy
Posting Yak Master

248 Posts

Posted - 2014-01-28 : 04:01:02

real time, whenever a customer is transfered the user will click in the application transfer so the suctomer will be moved to another location.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 06:01:54

quote:
Originally posted by shaggy

real time, whenever a customer is transfered the user will click in the application transfer so the suctomer will be moved to another location.

then you need to do this by means of a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources