Move table structure from sqlserver to oracle

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

Move table structure from sqlserver to oracle

Author

Topic

govindts
Starting Member

33 Posts

Posted - 2008-12-17 : 08:29:27

I am new to sqlserver... I have two hundred tables in sqlserver and i want to move these to oracle. I need to move only sturucture now. Please let me know what is the easy way to do this..

My option is,

open sqlserver management studio
click the databases
click the tables
open another window with create script option
create the script in oracle..

It is time consuming option. Any other methods?

Please advise.

JamesRyan
Starting Member

18 Posts

Posted - 2008-12-17 : 10:46:00

This might help, although tbh I haven't tested it...

http://www.softforall.com/SoftwareDev/DatabasesNetworks/SwisSQL_SQL_Server_to_Oracle08040073.htm

James Ryan
www.sqlhowto.co.uk

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-17 : 11:24:50

I got this from somewhere to script entire database:

DECLARE @Table_Definition VARCHAR(MAX),

@TableName VARCHAR(25)

DECLARE TableCursor CURSOR FOR

select table_catalog + '.' + table_schema + '.' + table_name as TableName
from information_schema.tables
where table_type='base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' +

QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'

SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE('

SELECT @Table_Definition = COALESCE(@Table_Definition,'') +

CHAR(13) + Column_Name + SPACE(1) + UPPER(Data_Type) +

CASE

WHEN data_type IN ('REAL','money','DECIMAL','NUMERIC') THEN

'(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+

CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')'

WHEN data_type IN ('CHAR','nvarchar','VARCHAR','NCHAR') THEN

'(' + CAST(isnull(Character_Maximum_Length,'') AS VARCHAR) + ')'

ELSE '' END +

CASE WHEN Is_Nullable='NO' THEN ' NOT NULL,' ELSE ' NULL,' END

FROM Information_Schema.Columns

WHERE Table_Name = @TableName

SET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')

PRINT @Table_Definition

--EXEC (@Table_Definition)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

govindts
Starting Member

33 Posts

Posted - 2008-12-17 : 11:39:29

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources