articles >> general dba >> Migrating from Oracle to SQL Server ...

Migrating from Oracle to SQL Server

By : Nagabhushanam Ponnapalli Nov 22, 2003

Page 3 / 5

Scripts

-- Script to Create Tables

BEGIN

SET NOCOUNT ON

DECLARE

@sqlStatement NVARCHAR(4000),

@tmpSQL NVARCHAR(4000),

@i INT,

@table_name VARCHAR(40),

@column_Name VARCHAR(40),

@data_default VARCHAR(250),

@seq_name VARCHAR(30),

@last_value INT,

@error INT,

@isMinCol BIT,

@data_type VARCHAR(50),

@data_type1 VARCHAR(50),

@isNullable CHAR(1),

@isMaxCol BIT,

@data_precision INT,

@data_scale INT,

@data_length INT

DECLARE cur_CreateTab CURSOR LOCAL READ_ONLY FOR

SELECT * FROM OPENQUERY (ORALINKSERVER,

'SELECT cl.table_name,

DECODE(cl.column_id, (gr.min_col),1,0) isminCol, cl.column_name,

cl.data_type,

NVL(cl.data_length,0),

NVL(cl.data_precision,0),

NVL(cl.data_scale,0),

cl.nullable,

decode(cl.column_id, (max_col), 1,0 ) ismaxcol

FROM user_tab_columns cl ,

user_objects uo, (

SELECT table_name, min(column_id) min_col,

max(column_id) max_col

FROM user_tab_columns

GROUP BY table_name) gr

WHERE cl.table_name = gr.table_name

AND cl.table_name = uo.object_name

AND uo.object_type = ''TABLE'' order by cl.table_name,cl.column_id')

OPEN cur_CreateTab

WHILE 1 < 2

BEGIN

SET @table_name = ''

SET @isMinCol = null

SET @column_name = NULL

SET @data_type = NULL

SET @data_type1 = NULL

SET @isNullable = NULL

SET @isMaxCol = NULL

SET @data_precision = null

SET @data_scale = null

SET @data_length = null

FETCH NEXT FROM cur_CreateTab

INTO @table_name, @isMinCol, @column_name, @data_type,

@data_length, @data_precision, @data_scale, @isNullable,

@isMaxCol

IF @@FETCH_STATUS <> 0

BREAK

IF @isMinCol = 1

BEGIN

SET @sqlStatement = ' Create Table [' + @table_name +']('

-- Print @sqlStatement

END

SET @sqlStatement = ISNULL(@sqlStatement,'') + ' ['+@Column_name+'] '

SELECT @data_Type1 = CASE @data_type

WHEN 'CHAR' THEN 'CHAR('+CAST(@data_length as VARCHAR)+')'

WHEN 'VARCHAR' THEN 'VARCHAR('+CAST(@data_length AS VARCHAR)+')'

WHEN 'VARCHAR2' THEN 'VARCHAR('+CAST(@data_length AS VARCHAR)+')'

WHEN 'LONG RAW' THEN 'IMAGE'

WHEN 'LONG' THEN 'TEXT'

WHEN 'DATE' THEN 'DATETIME'

WHEN 'BLOB' THEN 'IMAGE'

WHEN 'NUMBER' THEN CASE @data_precision

WHEN 0 THEN 'FLOAT'

ELSE 'DECIMAL('+ CAST(@data_precision as VARCHAR)+','+CAST( @data_scale as VARCHAR)+')'

END

WHEN 'FLOAT' THEN 'FLOAT'

ELSE @data_type

END

SET @sqlStatement = @sqlStatement +' '+ @data_type1

IF @isNullable = 'N'

SET @sqlStatement = ISNULL(@sqlStatement,'') + ' NOT NULL '

IF @isMaxCol = 1

BEGIN

SET @sqlStatement = ISNULL(@sqlStatement,'') + ')'

EXEC sp_ExecuteSql @sqlStatement

SET @sqlStatement = ''

END

ELSE

SET @sqlStatement = ISNULL(@sqlStatement,'') + ','

END

END

Print 'Handling the Collation....'

BEGIN

DECLARE

@sqlStatement NVARCHAR(1000)

DECLARE Cur_Collate CURSOR LOCAL READ_ONLY FOR

SELECT 'ALTER TABLE ['+ object_name(a.id)+ '] ALTER COLUMN ['+ a.name+ '] '

+c.name+

ISNULL(CASE c.name

WHEN 'TEXT' THEN NULL

WHEN 'NTEXT' THEN NULL

ELSE '('+CAST(a.length AS VARCHAR)+')'

END,' ')+ ' COLLATE SQL_Latin1_General_CP1_CS_AS '+ ISNULL(CASE isnullable

WHEN 0 THEN ' NOT NULL'

ELSE null

END, '')

FROM syscolumns a, sysobjects b, systypes c

WHERE a.id = b.id

and user_name(b.uid) = 'dbo'

and b.xtype = 'U'

and a.xtype= c.xtype

and a.xtype IN (167,239,175,231)

Order by object_name(a.id)

OPEN Cur_Collate

WHILE 1 < 2

BEGIN

FETCH NEXT FROM cur_collate INTO @sqlStatement

IF @@FETCH_STATUS <> 0

BREAK

EXEC sp_executeSQL @sqlStatement

END

SET @sqlStatement = ''

END

--Handling Sequences

BEGIN

Print ''

Print 'Now Creating the Sequence table...'

DECLARE

@seq_name VARCHAR(30),

@sqlStatement NVARCHAR(1000),

@last_value INT,

@error INT

DECLARE cur_seq CURSOR LOCAL READ_ONLY FOR

SELECT * FROM OPENQUERY(ORALINKSERVER,

'SELECT sequence_name,last_number seq

FROM user_sequences')

SET @sqlStatement = ' CREATE TABLE dbo.SEQUENCE_TAB (' + 'seq_name varchar (30) NOT NULL , '

+'seq_value bigint NOT NULL ,' +

'CONSTRAINT XPK_SEQUENCE_TAB PRIMARY KEY '+'

CLUSTERED (seq_name ))'

EXEC sp_executeSQL @sqlStatement

OPEN cur_seq

WHILE 1 < 2

BEGIN

FETCH NEXT FROM cur_seq INTO @seq_name, @last_Value

IF @@FETCH_STATUS <> 0

BREAK

SET @sqlStatement = 'INSERT INTO SEQUENCE_TAB VALUES('''+@seq_name+''',' +CAST(@last_Value AS VARCHAR)+ ' )'

EXEC sp_executeSQL @sqlStatement

END

END

Print 'Procedure to get next sequence value...'

CREATE PROCEDURE fnGetAutoID

@sTableName VARCHAR(40)

AS

BEGIN TRAN

SET NOCOUNT ON

DECLARE @sSeqNum VARCHAR(40)

IF @sTableName IS NULL

RETURN

ELSE

SELECT @sSeqNum = CAST(ISNULL(seq_value,0)+1 AS VARCHAR)

FROM Sequence_Tab WITH (HOLDLOCK ROWLOCK )

WHERE seq_name = UPPER(@sTableName)

IF @sSeqNum IS NOT NULL

UPDATE Sequence_Tab

SET seq_value = @sSeqNum

WHERE seq_name = UPPER(@sTableName)

IF @@error <> 0

RETURN NULL

COMMIT TRAN

RETURN CAST(@sSeqNum AS INT)

Print 'Now Creating defaults...'