Migrating from Oracle to SQL Server

Print ‘Now Creating the Constraints (Primary Key, Unique, Foreign Key and Check Constraints…’

SET NOCOUNT ON
DECLARE
@sqlStatement NVARCHAR(1000),
@tmpSQL NVARCHAR(1000),
@table_name VARCHAR(40),
@index_name VARCHAR(40),
@r_cons_name VARCHAR(40),
@column_name VARCHAR(40),
@ind_typ VARCHAR(2),
@is_unique VARCHAR(1),
@CheckCons VARCHAR(2000),
@r_table_name VARCHAR(40),
@delete_rule VARCHAR(10),
@error INT

DECLARE @tmpTable TABLE (
table_name VARCHAR(30),
Cons_name VARCHAR(30),
column_name VARCHAR(30),
column_position TINYINT)

DECLARE cur_tab_ind CURSOR LOCAL READ_ONLY FOR
SELECT table_name, constraint_name , ind_typ, is_unique
FROM OPENQUERY (ORALINKSERVER,
‘SELECT table_name, constraint_name , constraint_type ind_typ, ”U” is_unique, ”N” Check1
FROM user_constraints
WHERE constraint_type IN (”P”,”U”)
UNION
SELECT table_name, index_name,”N”,”N”, ”Y”
FROM user_indexes
WHERE uniqueness <> ”UNIQUE” ORDER BY 1,3 desc’) a, sysobjects b
where table_name = UPPER(OBJECT_NAME(id))
order by 1,3 desc

INSERT INTO @tmpTable
SELECT * FROM OPENQUERY (ORALINKSERVER,
‘ SELECT i.table_name,i.index_name, column_name,column_position
FROM user_ind_columns c, user_indexes i
WHERE i.index_name = c.index_name
and i.table_name = c.table_name
and uniqueness <> ”UNIQUE”
union
SELECT uc.table_name,uc.constraint_name, column_name,ucc.position
FROM user_cons_columns ucc, user_constraints uc
WHERE uc.table_name = ucc.table_name
and uc.constraint_name = ucc.constraint_name
and constraint_type IN (”P”,”U”)
ORDER BY 1,2,4′)

OPEN cur_tab_ind
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_tab_ind INTO
@table_name, @index_name, @ind_typ, @is_unique
IF @@FETCH_STATUS <> 0
BREAK

SET @sqlStatement = ‘ ‘
SET @tmpSQL = NULL

IF @ind_typ = ‘P’
BEGIN
IF @table_name = @index_name
SET @sqlStatement = ‘ALTER TABLE ['+@table_name +'] WITH NOCHECK ADD CONSTRAINT [PK_'+@index_name+'] PRIMARY KEY CLUSTERED (‘
ELSE
SET @sqlStatement = ‘ALTER TABLE ['+@table_name +'] WITH NOCHECK ADD CONSTRAINT ['+@index_name+'] PRIMARY KEY CLUSTERED (‘

END
ELSE
BEGIN
IF @is_unique = ‘N’
SET @sqlStatement = ‘CREATE NONCLUSTERED INDEX ‘
ELSE
SET @sqlStatement = ‘CREATE UNIQUE NONCLUSTERED INDEX ‘

IF @table_name = @index_name
SET @sqlStatement = @sqlStatement + ‘UN_’ + @index_name + ‘ ON [' + @table_name+'](‘
ELSE
SET @sqlStatement = @sqlStatement + @index_name + ‘ ON [' + @table_name+'](‘
END

SELECT @tmpSQL = COALESCE(@tmpSQL+’,',”)+ ‘['+column_name+']‘
FROM @tmpTable
WHERE table_name = @table_name
AND cons_name = @index_name
ORDER BY column_position

IF @ind_typ = ‘U’
SET @sqlStatement = @sqlStatement + @tmpSQL+’)’
ELSE
SET @sqlStatement = @sqlStatement + @tmpSQL+’) ON [PRIMARY]‘
–Print @sqlStatement

EXEC sp_executeSQL @sqlstatement
END

Print ”
Print ‘Now Creating the Check constraints….’
Print ”

DECLARE cur_Check_cons CURSOR LOCAL READ_ONLY FOR
SELECT ‘ALTER table ['+ a.table_name + '] WITH NOCHECK ADD Constraint ['+
constraint_name+'] ‘+ ‘ CHECK (‘ ,a.search_condition FROM OPENQUERY (ORALINKSERVER,
‘SELECT k.table_name, k.constraint_name,k.search_condition,c.column_name
FROM user_constraints k, user_cons_columns c
WHERE k.constraint_name = c.constraint_name
and k.table_name = c.table_name
and k.constraint_type = ”C” ‘) a, sysobjects
WHERE a.table_name = OBJECT_NAME(id)
AND a.search_condition NOT LIKE ‘%IS NOT NULL’

OPEN cur_check_cons
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_Check_Cons INTO @sqlStatement, @CheckCons
IF @@FETCH_STATUS <> 0
BREAK
SET @sqlStatement = @sqlStatement+ @CheckCons + ‘)’
Print @sqlStatement
EXEC sp_executeSQL @sqlStatement
END

Print ”
Print ‘Now Creating Foreign Key constraints….’
Print ”
DECLARE cur_Fkeys CURSOR LOCAL READ_ONLY FOR
SELECT ‘ALTER TABLE ['+table_name +'] Add Constraint ['+ constraint_name +'] FOREIGN KEY (‘,
table_name, constraint_name, r_constraint_name, r_table_name, delete_rule
FROM OPENQUERY (ORALINKSERVER,
‘SELECT k.table_name,k.constraint_name,k.r_constraint_name, r.table_name r_table_name,
k.delete_rule
FROM user_constraints k, user_constraints r
WHERE k.r_constraint_name = r.constraint_name
and k.constraint_type = ”R”
and r.constraint_type IN (”P”,”U”) ORDER BY 1′)a, sysobjects
WHERE a.table_name = OBJECT_NAME(id)

DELETE FROM @tmpTable

INSERT INTO @tmpTable
SELECT table_name, constraint_name, column_name, position
FROM OPENQUERY (ORALINKSERVER,
‘ SELECT c.table_name, c.constraint_name,c.column_name,c.position
FROM user_cons_columns c, user_constraints k
where c.constraint_name = k.constraint_name
and c.table_name = k.table_name
and k.constraint_type IN (”P”,”U”,”R”) ‘)

OPEN cur_Fkeys
WHILE 1 < 2
BEGIN
SET @sqlStatement = ”
FETCH NEXT FROM cur_Fkeys INTO
@sqlStatement, @table_name, @index_name, @r_cons_name, @r_table_name, @delete_rule
IF @@FETCH_STATUS <> 0
BREAK

SET @CheckCons = NULL

SELECT @CheckCons = COALESCE(@CheckCons+’,',”)+ ‘['+column_name+']‘
FROM @tmpTable
WHERE table_name = @table_name
AND cons_name = @index_name
ORDER BY column_position

SET @sqlStatement = @sqlStatement + @CheckCons+’) REFERENCES ['+ @r_table_name + '](‘

SET @CheckCons = null
SELECT @CheckCons = COALESCE(@CheckCons+’,',”)+ ‘['+column_name+']‘
FROM @tmpTable
WHERE table_name = @r_table_name
AND cons_name = @r_cons_name
ORDER BY column_position
SET @sqlStatement = @sqlStatement + @CheckCons+’) on Delete ‘+@delete_rule
–Print @sqlStatement
EXEC sp_executeSQL @sqlstatement
END
DELETE FROM @tmpTable

Print ‘Foreign Key Constraints Created…..’
Print ‘End of Migration process…..’

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 |