Migrating from Oracle to SQL Server

– Table Defaults

BEGIN

DECLARE

@sqlStatement NVARCHAR(1500),

@table_name VARCHAR(30),

@data_default VARCHAR(4000),

@column_name VARCHAR(30)

SET @sqlStatement =”

DECLARE cur_tab_default CURSOR LOCAL READ_ONLY FOR

SELECT * FROM OPENQUERY(ORALINKSERVER,

‘SELECT u.table_name, u.column_name, u.data_default

FROM user_tab_columns u, user_tables t

WHERE u.table_name = t.table_name

AND u.data_default IS NOT NULL ‘ )

OPEN cur_Tab_default

WHILE 1 < 2

BEGIN

FETCH NEXT FROM cur_Tab_default

INTO @table_name, @column_name, @data_default

IF @@FETCH_STATUS <> 0

BREAK

IF @data_default = ‘SYSDATE’

SET @data_default = ‘GETDATE()’

SET @sqlStatement = ‘ALTER TABLE ‘+@table_name+ ‘ ADD CONSTRAINT def_’+

@table_name+’_'+@column_name +’ DEFAULT ‘ +’ ‘+

@data_default + ‘ FOR ['+ @column_name+']‘

EXEC sp_executeSQL @sqlStatement

–Print @sqlStatement

END

Print ‘Successfully created defaults…’

END

BEGIN

Print ”

Print ‘Now Transferring the DATA from Oracle…..’

Print ”

DECLARE

@table_name VARCHAR(30),

@sqlStatement NVARCHAR(1000),

@error INT

DECLARE cur_DTS CURSOR LOCAL READ_ONLY FOR

SELECT * FROM OPENQUERY(ORALINKSERVER,

‘SELECT DISTINCT u.table_name

FROM user_tab_columns u, user_tables t

WHERE u.table_name = t.table_name

AND data_type NOT IN (”LONG RAW”,”BLOB”,”LONG”)’)

OPEN cur_DTS

WHILE 1 < 2

BEGIN

FETCH NEXT FROM cur_DTS INTO @table_name

IF @@FETCH_STATUS <> 0

BREAK

–Print ‘Transferring Data for table ‘ +@table_name +’….’

SET @sqlStatement = ‘INSERT INTO ['+ @table_name+']‘ +

‘ SELECT * FROM ORALINKSERVER..DBO.['+@table_name+']‘

EXEC sp_executeSQL @sqlStatement

SET @error = @@ERROR

IF @error <> 0

Print ‘Check this table ‘+@table_name

END

END

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |