Adbrite

Wednesday, December 30, 2009

Move Data one table to another table.

When we comes to Table designing and the table is very large table, then we can consider Table-Partitioning is most important factor. using above concept we can gain huge Performance and very easy to manage table containing records. In this topic I’m not going to discuss all about Table-Partitioning. But this is more related Table-Partitioning. If we want to move one particular range of data or all records of data one table to another table then Table-Partitioning come and play big role. This is less time consuming process.

/* Formatted on 2009/12/30 20:36 (Formatter Plus v4.8.7) */
CREATE TABLE sourceTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
)
PARTITION BY RANGE (column_num)
(
PARTITION tab_h1 VALUES LESS THAN
(11),
PARTITION tab_h2 VALUES LESS THAN
(21)
);

--Insert test Records
INSERT INTO sourceTable
SELECT ROWNUM, somecode
FROM (SELECT ROWNUM noofrows,'TD' somecode
FROM dba_objects)
WHERE noofrows < 21;

COMMIT ;

--Check no of records in Table
SELECT count(*)
FROM sourceTable;

SELECT *
FROM sourceTable;



--Create test Table 2
CREATE TABLE DestinationTable(
column_num NUMBER(18,0),
column_chr VARCHAR2(2)
);

--check step 1
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h1
WITH TABLE DestinationTable;

--or check step 2
ALTER TABLE sourceTable
EXCHANGE PARTITION tab_h2
WITH TABLE DestinationTable;


SELECT *
FROM sourcetable;

SELECT *
FROM DestinationTable;



--drop test tables
DROP TABLE sourceTable;
DROP TABLE destinationTable;



Many time we have done “create table as select …“(PL/SQL), “Inseet into select…”(T/SQL) commands to move data one table to new table or given table. But above methods high costly and time consuming operations (EXCHANGE PARTITION command just take 1 ms to move millions of records). Good news is SQL Server also support above PARTITION method but only SQL Server 2005 and 2008 versions only.

6 comments:

Anonymous said...

Hi There I'd love to congratulate you for such a great made forum!
Was thinking this would be a perfect way to introduce myself!

Sincerely,
Monte Phil
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/bowling-party-supplies.html]bowling Party Supplies[/url].

Anonymous said...

Do we need to re-build indexes after that ? Does Sqlserver have local index concept ?

Wasim Iqbal

Tharindu Dhaneenja said...

Hi Wasim,
you are totally correct after this process we need to REBUILD our index. because after finish this process all index become UNUSABLE State.
Check this,
--
SELECT index_name, index_type, table_name, status
FROM user_indexes
WHERE index_name='your index name';
--

Solution,
1. Drop the specified index and/or recreate the index
2. Rebuild the specified index


sql server 2005 and latest versions support Partitioned Tables and Indexes concept.
http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx

Thank you

sLars said...

If on oracle use the data pump. Great tool to move data to different enviroments, like getting parts of production data to test environment.

lee woo said...

Music is therapy. Music moves people. It connects people in ways that no other medium can. It pulls heart strings. It acts as medicine. See the link below for more info.

#moves
www.matreyastudios.com

Cindy Dy said...

Thank you for this post. Keep it up. Hope to read more post from you guys.

Viena
www.gofastek.com