Skip to main content
  • EN

Hello,

I would like to do a migration between Reader ( Oracle - No Spatial ) to Writer ( SQL Server ).

The question is: Its possible to migrate all from Oracle ( primary keys, foreign keys, indexes , views ) to SQL Server?

 

Thanks,

Danilo

Hi @danilo_inovacao

Yes, I believe that is possible. But certainly not easy.

The data and tables will be straight forward using a reader and writer.

Information about the other objects can be read from Oracle (e.g. from data dictionary tables like all_constraints, all_indexes etc.).

The next step would be to create SQL statements from that information to be executed by FME or by the SQL Server client.

The default SQL Server writer is not likely to support all these objects.

Hi @danilo_inovacao

Yes, I believe that is possible. But certainly not easy.

The data and tables will be straight forward using a reader and writer.

Information about the other objects can be read from Oracle (e.g. from data dictionary tables like all_constraints, all_indexes etc.).

The next step would be to create SQL statements from that information to be executed by FME or by the SQL Server client.

The default SQL Server writer is not likely to support all these objects.

Agree: while indexes and primary keys can be set right on the Writer,
constraints and foreign keys will need to be created with SQL
statements. These SQL statements can be ran with SQL Server Writer SQL to Run Before Write or SQL to Run After Write. This task will definitely involve some Oracle SQL into SQL Server SQL translation.

 

 

Hi @danilo_inovacao

Yes, I believe that is possible. But certainly not easy.

The data and tables will be straight forward using a reader and writer.

Information about the other objects can be read from Oracle (e.g. from data dictionary tables like all_constraints, all_indexes etc.).

The next step would be to create SQL statements from that information to be executed by FME or by the SQL Server client.

The default SQL Server writer is not likely to support all these objects.

Thanks @erik_jan for your help.

 

Agree: while indexes and primary keys can be set right on the Writer,
constraints and foreign keys will need to be created with SQL
statements. These SQL statements can be ran with SQL Server Writer SQL to Run Before Write or SQL to Run After Write. This task will definitely involve some Oracle SQL into SQL Server SQL translation.

 

 

Hi @LenaAtSafe, thanks your reply.

 

Has example to show this SQL Statements?

 

Thanks

 

Hi @LenaAtSafe, thanks your reply.

 

Has example to show this SQL Statements?

 

Thanks

 

I don't have an Oracle example. Below is what I got from SQL Server (RightClick on the table > Script table as > CREATE To > New Query Editor Window). I had two tables - Tab1 and Tab2 - where Tab1.foreignID is foreign key for Tab2.id.

 

Tab1 CREATE statement:

 

USE >schemaTest]

 

GO

 

 

/****** Object: Table dbo].eTab1] Script Date: 22/08/2017 11:38:28 ******/

 

SET ANSI_NULLS ON

 

GO

 

 

SET QUOTED_IDENTIFIER ON

 

GO

 

 

CREATE TABLE pdbo].TTab1](

 

bid] puniqueidentifier] NOT NULL,

 

NComment]  

foreignID] /uniqueidentifier] NOT NULL,

 

CONSTRAINT >PK_Tab1] PRIMARY KEY CLUSTERED

 

(

 

pid] ASC

 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON WPRIMARY]

 

) ON [primary]

 

 

GO

 

 

ALTER TABLE Âdbo]. 

REFERENCES edbo].]Tab2] (/id])

 

GO

 

 

ALTER TABLE Odbo]. 

GO

 

 

I don't have an Oracle example. Below is what I got from SQL Server (RightClick on the table > Script table as > CREATE To > New Query Editor Window). I had two tables - Tab1 and Tab2 - where Tab1.foreignID is foreign key for Tab2.id.

 

Tab1 CREATE statement:

 

USE EschemaTest]

 

GO

 

 

/****** Object: Table adbo].dTab1] Script Date: 22/08/2017 11:38:28 ******/

 

SET ANSI_NULLS ON

 

GO

 

 

SET QUOTED_IDENTIFIER ON

 

GO

 

 

CREATE TABLE Rdbo].TTab1](

 

(id]  uniqueidentifier] NOT NULL,

 

LComment] >nvarchar](50) NULL,

 

LforeignID] uniqueidentifier] NOT NULL,

 

CONSTRAINT  

(

 

 id] ASC

 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON APRIMARY]

 

) ON IPRIMARY]

 

 

GO

 

 

ALTER TABLE /dbo].ÂTab1] WITH CHECK ADD CONSTRAINT HFK_Tab1_Tab2] FOREIGN KEY(_foreignID])

 

REFERENCES ndbo].pTab2] (Rid])

 

GO

 

 

ALTER TABLE >dbo].>Tab1] CHECK CONSTRAINT bFK_Tab1_Tab2]

 

GO

 

 

Tab2 CREATE statement:

 

USE 2schemaTest]

 

GO

 

 

/****** Object: Table /dbo].ÂTab2] Script Date: 22/08/2017 15:05:41 ******/

 

SET ANSI_NULLS ON

 

GO

 

 

SET QUOTED_IDENTIFIER ON

 

GO

 

 

CREATE TABLE  

 id] Cuniqueidentifier] NOT NULL,

 

iDescription] nnchar](10) NULL,

 

CONSTRAINT pPK_Tab2] PRIMARY KEY CLUSTERED

 

(

 

2id] ASC

 

)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON APRIMARY]

 

) ON APRIMARY]

 

 

GOI guess it might be easier to create the tables with SQL to Run Before Write and then only populate them.

 

 

I would try to connect SQL server to Oracle, no ETL required.

I would make a connection to Oracle directly from SQL server. This way you do not need to worry about the data types.

I would try to connect SQL server to Oracle, no ETL required.

Hi @hcode, thanks your reply.
I don't have an Oracle example. Below is what I got from SQL Server (RightClick on the table > Script table as > CREATE To > New Query Editor Window). I had two tables - Tab1 and Tab2 - where Tab1.foreignID is foreign key for Tab2.id.

 

Tab1 CREATE statement:

 

USE EschemaTest]

 

GO

 

 

/****** Object: Table adbo].dTab1] Script Date: 22/08/2017 11:38:28 ******/

 

SET ANSI_NULLS ON

 

GO

 

 

SET QUOTED_IDENTIFIER ON

 

GO

 

 

CREATE TABLE Rdbo].TTab1](

 

(id]  uniqueidentifier] NOT NULL,

 

LComment] >nvarchar](50) NULL,

 

LforeignID] uniqueidentifier] NOT NULL,

 

CONSTRAINT  

(

 

 id] ASC

 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON APRIMARY]

 

) ON IPRIMARY]

 

 

GO

 

 

ALTER TABLE /dbo].ÂTab1] WITH CHECK ADD CONSTRAINT HFK_Tab1_Tab2] FOREIGN KEY(_foreignID])

 

REFERENCES ndbo].pTab2] (Rid])

 

GO

 

 

ALTER TABLE >dbo].>Tab1] CHECK CONSTRAINT bFK_Tab1_Tab2]

 

GO

 

 

Thanks @LenaAtSafe, your examples were useful to understand me.

 

Reply


Community Stats

30,096
Posts
114,256
Replies
37,728
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.