After you have recreated all missing tables, indexes, and constraints, you are ready to migrate the data from the Oracle database into your staging database (INFODBA) in SQL Server. To migrate the data, you need to create a series of INSERT INTO statements to read the data from the Oracle tables into the SQL Server staging tables. Once you have moved the data into the staging tables, you will then move the data into the production SQL Server tables.
Note: Appendix B provides some common table creation scripts.
1. Execute the following script to move the data into the staging database: USE INFODBA
GO
SET NOCOUNT ON
DECLARE @table_count BIGINT DECLARE @table_name VARCHAR(255) DECLARE @strsql nvarchar(4000) DECLARE @strsql2 VARCHAR(4000) BEGIN TRY
DROP TABLE #tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #tables
(
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) )
BEGIN TRY
DROP TABLE #columns
END TRY BEGIN CATCH
48 END CATCH
CREATE TABLE #columns
(
colid INT IDENTITY(1,1) ,column_name VARCHAR(255) ,system_type_id INT
)
INSERT INTO #tables (table_name)
SELECT TABLE_NAME FROM ORACLETC..SYS.USER_TABLES
WHERE TABLE_NAME <> 'PLAN_TABLE'
SET @table_count = @@ROWCOUNT WHILE @table_count > 0
BEGIN
TRUNCATE TABLE #columns
SELECT @table_name = table_name FROM #tables WHERE colid =
@table_count
SET @strsql = 'INSERT INTO #columns (column_name,system_type_id) SELECT name, system_type_id FROM sys.columns WHERE object_id = OBJECT_ID('''
+ @table_name + ''')'
EXEC dbo.sp_executesql @strsql
UPDATE #columns
SET column_name = 'CAST(' + column_name + ' AS VARCHAR(1000))'
WHERE system_type_id IN (108) SET @strsql2 = ''
SELECT @strsql2 = @strsql2 + column_name + ','
FROM #columns
SET @strsql2 = @strsql2 + '|' --take care of trailing comma
SET @strsql2 = REPLACE (@strsql2,',|',' ')
SET @strsql = 'INSERT INTO ' + @table_name
+ ' SELECT * FROM OPENQUERY(ORACLETC,''SELECT ' + @strsql2 + ' FROM INFODBA.'
+ @table_name + ''') AS ROWSET_1'
BEGIN TRY
EXEC dbo.sp_executesql @strsql
END TRY BEGIN CATCH SELECT @strSQL END CATCH SET @table_count -= 1 END
49 USE INFODBA
GO
SET NOCOUNT ON BEGIN TRY
DROP TABLE #ora_tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #ora_tables (
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,row_count BIGINT
)
BEGIN TRY
DROP TABLE #sql_tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #sql_tables (
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,row_count BIGINT
)
DECLARE @ora_table_count INT DECLARE @sql_table_count INT DECLARE @table_name VARCHAR(255) DECLARE @strSQL NVARCHAR(4000) INSERT INTO #sql_tables(table_name) SELECT name FROM sys.objects
WHERE is_ms_shipped = 0
AND type = 'U'
SET @sql_table_count = @@ROWCOUNT WHILE @sql_table_count > 0
BEGIN
SELECT @table_name = table_name FROM #sql_tables WHERE colid =
@sql_table_count
SET @strSQL = 'UPDATE #sql_tables SET row_count = (SELECT COUNT(*) FROM dbo.' + @table_name + ' WITH (NOLOCK)) WHERE colid = ' + CAST(@sql_table_count AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @sql_table_count -= 1
END
INSERT INTO #ora_tables(table_name) SELECT TABLE_NAME
FROM ORACLETC..SYS.USER_TABLES
50 WHILE @ora_table_count > 0
BEGIN
SELECT @table_name = table_name FROM #ora_tables WHERE colid =
@ora_table_count
SET @strSQL = 'UPDATE #ora_tables SET row_count = (SELECT COUNT(*) FROM dbo.' + @table_name + ' WITH (NOLOCK)) WHERE colid = ' + CAST(@ora_table_count AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @ora_table_count -= 1
END
--Tables showing up have different row counts
SELECT sc.table_name AS 'Staging Table'
,sc.row_count AS 'Staging row count'
,oc.table_name AS 'Oracle Table'
,oc.row_count AS 'Oracle row count'
FROM #sql_tables sc
INNER JOIN #ora_tables oc
ON sc.table_name = oc.table_name
AND sc.row_count <> oc.row_count
3. Verify that the data precision is the same: USE INFODBA
GO
SET NOCOUNT ON BEGIN TRY
DROP TABLE #ora_tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #ora_tables (
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,column_name VARCHAR(255) ,ora_data_length INT ,sql_data_length INT )
DECLARE @ora_table_count INT DECLARE @loop INT
DECLARE @table_name VARCHAR(255) DECLARE @column_name VARCHAR(255) DECLARE @strSQL NVARCHAR(4000) SET @loop = 1
INSERT INTO #ora_tables(table_name,column_name) SELECT TABLE_NAME, COLUMN_NAME
FROM ORACLETC..SYS.USER_TAB_COLUMNS
WHERE DATA_TYPE = 'FLOAT'
AND DATA_PRECISION = 126
51 SET @loop = @ora_table_count
WHILE @loop > 0
BEGIN
SELECT @table_name = table_name, @column_name = column_name
FROM #ora_tables WHERE colid = @loop
SET @strSQL = 'UPDATE #ora_tables SET ora_data_length = (SELECT ISNULL(MAX(LEN(' + @column_name + ')),0) FROM ORACLETC..INFODBA.'
+ @table_name + ' WITH (NOLOCK))
WHERE colid = ' + CAST(@loop AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @loop -= 1
END
SET @loop = @ora_table_count
WHILE @loop > 0
BEGIN
SELECT @table_name = table_name, @column_name = column_name
FROM #ora_tables WHERE colid = @loop
SET @strSQL = 'UPDATE #ora_tables SET sql_data_length = (SELECT ISNULL(MAX(LEN(' + @column_name + ')),O) FROM dbo.' + @table_name
+ ' WITH (NOLOCK))
WHERE colid = ' + CAST(@loop AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @loop -= 1
END
--Should be no result set
SELECT *
FROM #ora_tables
WHERE ora_data_length <> sql_data_length
Note: You may have to manually correct any data precision issues by changing the data types of the production SQL Server database.
4. After migrating the data from the Oracle database into your staging database, create a backup of the staging database.
5. After the backup completes, you will need to create a snapshot database of the database for easier rollback to the migrated staging database in case of errors or issues during the migration to the production database.
USE master GO
--Determine file names
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('INFODBA') CREATE DATABASE INFODBA_snapshot
ON
52 ( NAME = INFODBA , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\INFODBA.ss' ) AS SNAPSHOT OF INFODBA; GO
6. After creating a snapshot of the staging database for rollback purposes, you will now need to rename several tables in the staging database schema to match the production database schema.
--Rename tables in staging to match production
USE INFODBA
GO
SET NOCOUNT ON DECLARE @loop INT DECLARE @loop_1 INT DECLARE @rowcount INT
DECLARE @command_1 NVARCHAR(4000) DECLARE @command_2 NVARCHAR(4000)
--There will be one set of commands for temporary names --There will be another set of commands for final names
SET @loop_1 = 2
--Table to hold rename commands
BEGIN TRY
DROP TABLE #PLOVP
END TRY BEGIN CATCH END CATCH
CREATE TABLE #PLOVP
(
colid INT IDENTITY(1,1)
,command_1 NVARCHAR(4000) COLLATE Latin1_General_BIN
,command_2 NVARCHAR(4000) COLLATE Latin1_General_BIN
)
--Determine the difference between SQL Server version and Oracle version
--Rename to SQL Server version
--Need to set tables to temp names to avoid renaming conflicts
INSERT INTO #PLOVP (command_1, command_2)
SELECT 'exec sp_rename ''' + infodba.pdbname COLLATE
Latin1_General_BIN + ''',''' + tc.pdbname + '_new'''
,'exec sp_rename '''+ tc.pdbname + '_new'''+ ',''' + tc.pdbname + ''''
FROM
(SELECT a.pdbname
,c.pname AS pname_class
,a.pname AS pname_attribute
FROM INFODBA..PPOM_CLASS_saved c
INNER JOIN INFODBA..PPOM_ATTRIBUTE_saved a
ON a.rdefining_classu = c.puid
53 WHERE a.pdbname LIKE 'PLOV_VALUES%') tc
INNER JOIN (SELECT a.pdbname
,c.pname AS pname_class
,a.pname AS pname_attribute
FROM INFODBA..PPOM_CLASS c
INNER JOIN INFODBA..PPOM_ATTRIBUTE a
ON a.rdefining_classu = c.puid
AND a.plength = -1
WHERE a.pdbname LIKE 'PLOV_VALUES%'
AND c.pname LIKE 'ListOf%') infodba
ON tc.pname_class = infodba.pname_class COLLATE Latin1_General_BIN
AND tc.pname_attribute = infodba.pname_attribute COLLATE
Latin1_General_BIN
SET @rowcount = @@ROWCOUNT WHILE @loop_1 > 0
BEGIN
SET @loop = @rowcount
--Execute rename statements
WHILE @loop > 0
BEGIN
SELECT @command_1 = command_1, @command_2 = command_2 FROM
#PLOVP WHERE colid = @loop
IF @loop_1 = 2
BEGIN
EXEC dbo.sp_executesql @command_1
END
IF @loop_1 = 1
BEGIN
EXEC dbo.sp_executesql @command_2
END SET @loop -= 1 END SET @loop_1 -= 1 END GO
7. After creating a snapshot of the staging database for rollback purposes, you will now need to rename several columns in the staging database schema to match the production database schema.
--Rename columns in staging to match production
USE INFODBA
GO
SET NOCOUNT ON
DECLARE @rowcount INT
DECLARE @command_1 NVARCHAR(4000)
54 BEGIN TRY
DROP TABLE #PLOVP
END TRY BEGIN CATCH END CATCH
CREATE TABLE #PLOVP
(
colid INT IDENTITY(1,1)
,command_1 NVARCHAR(4000) COLLATE Latin1_General_BIN
)
--Determine the column naming differences between SQL Server version and Oracle version
--Rename to SQL Server version
INSERT INTO #PLOVP (command_1)
SELECT 'EXEC dbo.sp_rename ''' + info.tabname_class + '.' + info.vla +
''',''' + tc.vla + ''',''COLUMN'''
FROM
(SELECT 'VLA_'+ CONVERT(VARCHAR, pc.pcpid) + '_' + CONVERT(VARCHAR,
pa.papid) AS vla
,pc.PNAME AS oname
,pa.PNAME AS descp
,pc.PTNAME AS tabname_class
FROM INFODBA..PPOM_CLASS pc
INNER JOIN INFODBA..PPOM_ATTRIBUTE pa
ON pa.rdefining_classu = pc.puid
AND pa.plength = -1) info
INNER JOIN
(SELECT 'VLA_'+ CONVERT(VARCHAR, pc.pcpid) + '_' + CONVERT(VARCHAR,
pa.papid) AS VLA
,pc.PNAME AS oname
,pa.PNAME AS descp
,pc.PTNAME AS tabname_class
FROM INFODBA..PPOM_CLASS_saved pc
INNER JOIN INFODBA..PPOM_ATTRIBUTE_saved pa
ON pa.rdefining_classu = pc.puid
AND pa.plength = -1) tc
ON info.oname COLLATE Latin1_General_BIN = tc.oname
AND info.descp COLLATE Latin1_General_BIN = tc.descp
AND info.tabname_class COLLATE Latin1_General_BIN = tc.tabname_class
SET @rowcount = @@ROWCOUNT WHILE @rowcount > 0
BEGIN
SELECT @command_1 = command_1 FROM #PLOVP WHERE colid = @rowcount
BEGIN TRY
EXEC dbo.sp_executesql @command_1
END TRY BEGIN CATCH END CATCH SET @rowcount -= 1 END GO
55 8. Update columns in the POM_INDEXES table.
--Determine and rename duplicate entries
USE INFODBA
GO
UPDATE TOP (1) d
SET d.dbname = d.dbname + '_' + CAST(d.youneek AS VARCHAR(11)) FROM POM_INDEXES d
INNER JOIN (
SELECT dbname,cpid,apid
FROM POM_INDEXES
GROUP BY dbname,cpid,apid
HAVING COUNT(*) > 1)db
ON db.dbname = d.dbname
AND db.cpid = d.cpid
AND db.apid = d.apid
9. Drop the temporary PPOM_CLASS_saved and PPOM_ATTRIBUTE_saved tables. USE INFODBA
GO
DROP TABLE PPOM_CLASS_saved
DROP TABLE PPOM_ATTRIBUTE_saved
GO
10. Once you are satisfied that everything is correct and have performed all use-case testing from your migration plan, migrate the data into the production database:
USE tc
GO
SET NOCOUNT ON
DECLARE @table_count BIGINT DECLARE @table_name VARCHAR(255) DECLARE @strsql nvarchar(4000) DECLARE @strsql2 VARCHAR(4000) BEGIN TRY
DROP TABLE #row_count
END TRY BEGIN CATCH END CATCH
CREATE TABLE #row_count
(
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,row_count BIGINT
56 BEGIN TRY
DROP TABLE #tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #tables
(
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) )
BEGIN TRY
DROP TABLE #columns
END TRY BEGIN CATCH END CATCH
CREATE TABLE #columns
(
colid INT IDENTITY(1,1) ,column_name VARCHAR(255) ,system_type_id INT
)
DECLARE @row_count INT
INSERT INTO #row_count(table_name) SELECT name FROM INFODBA.sys.objects
WHERE is_ms_shipped = 0
AND type = 'U'
SET @row_count = @@ROWCOUNT WHILE @row_count > 0
BEGIN
SELECT @table_name = table_name FROM #row_count WHERE colid =
@row_count
SET @strSQL = 'UPDATE #row_count SET row_count = (SELECT COUNT(*) FROM INFODBA.dbo.' + @table_name + ' WITH (NOLOCK))
WHERE colid = ' + CAST(@row_count AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @row_count -= 1
END
INSERT INTO #tables (table_name) SELECT so.name
FROM tc.sys.objects so
WHERE so.is_ms_shipped = 0
AND so.type = 'U'
SET @table_count = @@ROWCOUNT WHILE @table_count > 0
BEGIN
TRUNCATE TABLE #columns
57 SELECT @table_name = table_name FROM #tables WHERE colid =
@table_count
SELECT @row_count = row_count FROM #row_count WHERE table_name =
@table_name
SET @row_count = ISNULL(@row_count,0) IF @row_count > 0
BEGIN
SET @strsql = 'INSERT INTO #columns (column_name) SELECT name FROM tc.sys.columns WHERE object_id = OBJECT_ID('''
+ @table_name + ''')'
EXEC dbo.sp_executesql @strsql
SET @strsql2 = ''
SELECT @strsql2 = @strsql2 + column_name + ','
FROM #columns
SET @strsql2 = @strsql2 + '|' --take care of trailing comma
SET @strsql2 = REPLACE (@strsql2,',|',' ')
SET @strsql = 'INSERT INTO ' + @table_name + '(' + @strsql2
+ ')'
+ ' SELECT ' + @strsql2 + ' FROM INFODBA.dbo.' +
@table_name
BEGIN TRY
EXEC dbo.sp_executesql @strsql
END TRY BEGIN CATCH
SELECT @strSQL AS 'Table did not load investigate manually'
END CATCH END
SET @table_count -= 1
END
11. The script above will produce a listing of tables that did not have data into them due to column differences between the staging and production databases. You may have to manually create some table insert statements to overcome these column differences, as in these examples: USE tc
GO
--Column names different
INSERT INTO PPOM_SITE_CONFIG(puid,LS_13_2,pindex )
SELECT puid,LS_388_1,pindex FROM INFODBA.dbo.PPOM_SITE_CONFIG
--Column names different
INSERT INTO PTC_PREFERENCES(puid,LS_330_2,robject_tagu,robject_tagc )
SELECT puid,LS_473_1,robject_tagu,robject_tagc FROM
58
--Column names are different
INSERT INTO PFULLTEXT(puid,LS_739_1,pbody_cleartext,pcontent_type )
SELECT puid,LS_492_1,pbody_cleartext,pcontent_type FROM
INFODBA.dbo.PFULLTEXT
--Column PIPLISTOFVALUES_1_0 not in SQL Server table
INSERT INTO
PLISTOFVALUES(puid,plov_name,plov_desc,plov_type,plov_attached_class,pl ov_attached_attr,VLA_427_9,VLA_427_10,VLA_427_11,VLA_427_12,VLA_427_13,
VLA_427_14,VLA_427_15,VLA_427_16,VLA_427_17,VLA_427_18,VLA_427_19,VLA_4 27_20,plov_attached_type,plov_value_type,plov_usage )
SELECT
puid,plov_name,plov_desc,plov_type,plov_attached_class,plov_attached_at tr,VLA_427_9,VLA_427_10,VLA_427_11,VLA_427_12,VLA_427_13,VLA_427_14,VLA _427_15,VLA_427_16,VLA_427_17,VLA_427_18,VLA_427_19,VLA_427_20,plov_att ached_type,plov_value_type,plov_usage FROM INFODBA.dbo.PLISTOFVALUES
--Column PIPIMANQUERY_1_0 not in SQL Server table
INSERT INTO
PIMANQUERY(puid,pquery_name,pquery_desc,pquery_class,VLA_184_4,pquery_u id_name,pquery_flag,presults_type )
SELECT
puid,pquery_name,pquery_desc,pquery_class,VLA_184_4,pquery_uid_name,pqu ery_flag,presults_type FROM INFODBA.dbo.PIMANQUERY
--Column PIPITEMREVISION_2_0 not in SQL Server table
INSERT INTO
PITEMREVISION(puid,pitem_revision_id,VLA_631_8,VLA_631_10,VLA_631_11,VL A_631_12,rsequence_anchoru,rsequence_anchorc,rvariant_expression_blocku
,rvariant_expression_blockc,ritems_tagu,ritems_tagc,psequence_id,pseque nce_limit,phas_variant_module)
SELECT
puid,pitem_revision_id,VLA_631_8,VLA_631_10,VLA_631_11,VLA_631_12,rsequ ence_anchoru,rsequence_anchorc,rvariant_expression_blocku,rvariant_expr ession_blockc,ritems_tagu,ritems_tagc,psequence_id,psequence_limit,phas _variant_module FROM INFODBA.dbo.PITEMREVISION
--Columns PIPOM_KEY_0 and PIPOM_KEY_1 not in SQL Server table
INSERT INTO POM_KEY(puid,domain,key_value)
SELECT puid,domain,key_value FROM INFODBA.dbo.POM_KEY
--Column PIPPOM_USER_1_0 not in SQL Server table
INSERT INTO
PPOM_USER(puid,puser_id,ppassword,puser_name,pstatus,pdef_acl,pdefault_ group,puser_data_source,plicense_level,plast_login_time,puser_last_sync _date )
SELECT
puid,puser_id,ppassword,puser_name,pstatus,pdef_acl,pdefault_group,puse r_data_source,plicense_level,plast_login_time,puser_last_sync_date
FROM INFODBA.dbo.PPOM_USER
--Column difference
INSERT INTO
POM_ROOT(site_id,[schema],[path],site_state,cpid,[version],scan_state,s can_name,scan_class,info,can_edit_schema )
59 SELECT
site_id,[schema],[path],site_state,cpid,[version],scan_state,scan_name,
scan_class,info,can_edit_schema FROM INFODBA.dbo.POM_ROOT
--Columns PIPWORKSPACEOBJ_0_0 and PIPWORKSPACEOBJ_1_0 not in SQL Server table
INSERT INTO
PWORKSPACEOBJECT(puid,pobject_name,pobject_desc,pobject_type,pobject_ap plication,VLA_484_7,pip_classification,VLA_484_10,pgov_classification,V LA_484_13,VLA_484_16,pactive_seq,prevision_number,rwso_threadu,rwso_thr eadc,rowning_organizationu,rowning_organizationc,prevision_limit,rownin g_projectu,rowning_projectc,pdate_released )
SELECT
puid,pobject_name,pobject_desc,pobject_type,pobject_application,VLA_484 _7,pip_classification,VLA_484_10,pgov_classification,VLA_484_13,VLA_484 _16,pactive_seq,prevision_number,rwso_threadu,rwso_threadc,rowning_orga nizationu,rowning_organizationc,prevision_limit,rowning_projectu,rownin g_projectc,pdate_released FROM INFODBA.dbo.PWORKSPACEOBJECT
12. Verify the row counts between the staging and production SQL Server databases: USE INFODBA
GO
SET NOCOUNT ON BEGIN TRY
DROP TABLE #sql_tables_staging
END TRY BEGIN CATCH END CATCH
CREATE TABLE #sql_tables_staging
(
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,row_count BIGINT
)
BEGIN TRY
DROP TABLE #sql_tables_final
END TRY BEGIN CATCH END CATCH
CREATE TABLE #sql_tables_final
(
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,row_count BIGINT
)
DECLARE @stage_table_count INT DECLARE @final_table_count INT DECLARE @table_name VARCHAR(255) DECLARE @strSQL NVARCHAR(4000)
60 INSERT INTO #sql_tables_staging(table_name)
SELECT name FROM sys.objects
WHERE is_ms_shipped = 0
AND type = 'U'
SET @stage_table_count = @@ROWCOUNT WHILE @stage_table_count > 0
BEGIN
SELECT @table_name = table_name FROM #sql_tables_staging
WHERE colid = @stage_table_count
SET @strSQL = 'UPDATE #sql_tables_staging SET row_count = (SELECT COUNT(*) FROM dbo.' + @table_name + ' WITH (NOLOCK))
WHERE colid = ' + CAST(@stage_table_count AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @stage_table_count -= 1
END
INSERT INTO #sql_tables_final(table_name) SELECT name
FROM tc.sys.objects --you will have to change to your database name
WHERE is_ms_shipped = 0
AND type = 'U'
SET @final_table_count = @@ROWCOUNT WHILE @final_table_count > 0
BEGIN
SELECT @table_name = table_name FROM #sql_tables_final
WHERE colid = @final_table_count
SET @strSQL = 'UPDATE #sql_tables_final SET row_count = (SELECT COUNT(*) FROM tc.dbo.' + @table_name + ' WITH (NOLOCK))
WHERE colid = ' + CAST(@final_table_count AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @final_table_count -= 1
END
--Tables showing up have different row counts
SELECT sc.table_name AS 'Staging Table'
,sc.row_count AS 'Staging Row Count'
,fc.table_name AS 'Production Table'
,fc.row_count AS 'Production Table Row Count'
FROM #sql_tables_staging sc
INNER JOIN #sql_tables_final fc
ON sc.table_name = fc.table_name
AND sc.row_count <> fc.row_count
13. Verify that the data precision is the same: USE INFODBA
GO
SET NOCOUNT ON BEGIN TRY
61 DROP TABLE #staging_tables
END TRY BEGIN CATCH END CATCH
CREATE TABLE #staging_tables (
colid INT IDENTITY(1,1) ,table_name VARCHAR(255) ,column_name VARCHAR(255) ,staging_data_length INT ,final_data_length INT )
DECLARE @staging_table_count INT DECLARE @loop INT
DECLARE @table_name VARCHAR(255) DECLARE @column_name VARCHAR(255) DECLARE @strSQL NVARCHAR(4000) SET @loop = 1
INSERT INTO #staging_tables(table_name,column_name) SELECT TABLE_NAME, COLUMN_NAME
FROM ORACLETC..SYS.USER_TAB_COLUMNS
WHERE DATA_TYPE = 'FLOAT'
AND DATA_PRECISION = 126
SET @staging_table_count = @@ROWCOUNT SET @loop = @staging_table_count
WHILE @loop > 0
BEGIN
SELECT @table_name = table_name, @column_name = column_name
FROM #staging_tables WHERE colid = @loop
SET @strSQL = 'UPDATE #staging_tables SET staging_data_length = (SELECT ISNULL(MAX(LEN(' + @column_name + ')),0) FROM
INFODBA.dbo.' + @table_name + ' WITH (NOLOCK)) WHERE colid = ' + CAST(@loop AS VARCHAR(20))
EXEC sp_executesql @strSQL
SET @loop -= 1
END
SET @loop = @staging_table_count
WHILE @loop > 0
BEGIN
SELECT @table_name = table_name, @column_name =LOWER(column_name) FROM #staging_tables WHERE colid = @loop
--You will have to change production database name
BEGIN TRY
SET @strSQL = 'UPDATE #staging_tables SET final_data_length = (SELECT ISNULL(MAX(LEN(' + @column_name COLLATE
Latin1_General_BIN + ')),0) FROM tc.dbo.' + @table_name +
' WITH (NOLOCK))
WHERE colid = ' + CAST(@loop AS VARCHAR(20))
EXEC sp_executesql @strSQL
END TRY BEGIN CATCH
62 SELECT @table_name AS 'Table Name'
, @column_name AS 'Column Name'
END CATCH SET @loop -= 1
END
--Should be no result set
SELECT *
FROM #staging_tables
WHERE staging_data_length <> final_data_length
Note; You may have to manually correct any data precision issues. 14. Drop the staging database snapshot:
USE MASTER GO
DROP DATABASE INFODBA_snapshot
GO