— 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