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.

Unfair limitations of Oracle Database 10G

Unfair limitations of Oracle Database 10G

Oracle one of great database Product and it takes more than 50% of market share. But I’m honestly saying Oracle has very unfair limitations. Few of them are,

• Database name length (no of Digits) – 8

• Table, Procedure...Etc name length (no of Digits) – 30

But recently I was come across Oracle Installation Problem,

1. “Invalid command line argument. Execute VSJITDebuggar /? for Help”. Javaw.exe has encountered a Problem and need to close.
2. “An unhandled win32 exception occurred in Javaw.exe [5676] “

I was nearly spent more than 1 week but I couldn’t found any solution. Unfortunately there haven’t any blog or article related this. Luckily Buddy (Buddhika Karunatilake) find out the solution. The problem was my Oracle installation contain folder path too long. (“C:\Documents and Settings\Administrator\Desktop\Oracle\ Oracle-10.2.0.1.0-WinNT-Base\database”) I found few oracle articles about oracle limitation,

1. http://www.oracle.com/technology/products/ias/bpel/htdocs/soa_training_10133_installation_instructions.html - (Check 2. Installing the software)
2. http://download.oracle.com/docs/cd/B19188_01/doc/B15917/limitations.htm
3. http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
4. http://www.gc.maricopa.edu/business/oracle/docs/Oracle8iDocs/server.815/a67790/ch4.htm

SQL Server 2008 Books Online Has Small mistake.

Recently I was start to complete my Understanding the Basic of Triggers Article Part II hope to include all topics related Triggers in that case I want to explain COLUMNS_UPDATED (Transact-SQL) Syntax because everybody know it`s very useful when we working with triggers . Hope everybody familiar with COLUMNS_UPDATED (Transact-SQL) Syntax so I’m not going to explain it this time (after complete my Article then you can get clear idea so pls wait for while). I want to say there is small mistake in SQL Server 2008 Books Online (November 2009) under COLUMNS_UPDATED (Transact-SQL) category, example of “B. Using COLUMNS_UPDATED to test more than eight columns” under this headline. I’m honestly saying this is not big mistake but my view is Microsoft Technical writing and QA team needs to get responsibility of this mistake. So when we comes to this mistake,

USE AdventureWorks;
GO
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
DROP TRIGGER Person.tr1; <---mistake
GO
CREATE TRIGGER uContact2 ON Person.Contact
AFTER UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )
PRINT 'Columns 3, 5 and 9 updated';
GO

UPDATE Person.Contact
SET Title=Title, MiddleName=MiddleName,
EmailPromotion=EmailPromotion;
GO

If you run above SQL statement in first time your AdventureWorks database its runs successfully but if you run it again then it will gives you following error.

Msg 2714, Level 16, State 2, Procedure uContact2, Line 5 There is already an object named 'uContact2' in the database.

Hope now everybody will understand the mistake and it’s not big mistake either it’s not good for company like Microsoft.