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
Solved
Migration Oracle to SQL Server
+52- Celebrity
Best answer by erik_jan
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.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+23- Contributor
- Best Answer
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.
+52- Celebrity
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.
+52- Celebrity
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.
Has example to show this SQL Statements?
Thanks
Hi @LenaAtSafe, thanks your reply.
Has example to show this SQL Statements?
Thanks
Tab1 CREATE statement:
USE [schemaTest]
GO
/****** Object: Table [dbo].[Tab1] Script Date: 22/08/2017 11:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tab1](
[id] [uniqueidentifier] NOT NULL,
[Comment] [nvarchar](50) NULL,
[foreignID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab1] WITH CHECK ADD CONSTRAINT [FK_Tab1_Tab2] FOREIGN KEY([foreignID])
REFERENCES [dbo].[Tab2] ([id])
GO
ALTER TABLE [dbo].[Tab1] CHECK CONSTRAINT [FK_Tab1_Tab2]
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 [schemaTest]
GO
/****** Object: Table [dbo].[Tab1] Script Date: 22/08/2017 11:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tab1](
[id] [uniqueidentifier] NOT NULL,
[Comment] [nvarchar](50) NULL,
[foreignID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab1] WITH CHECK ADD CONSTRAINT [FK_Tab1_Tab2] FOREIGN KEY([foreignID])
REFERENCES [dbo].[Tab2] ([id])
GO
ALTER TABLE [dbo].[Tab1] CHECK CONSTRAINT [FK_Tab1_Tab2]
GO
USE [schemaTest]
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 [dbo].[Tab2](
[id] [uniqueidentifier] NOT NULL,
[Description] [nchar](10) NULL,
CONSTRAINT [PK_Tab2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOI guess it might be easier to create the tables with SQL to Run Before Write and then only populate them.
I would make a connection to Oracle directly from SQL server. This way you do not need to worry about the data types.
+52- Celebrity
I would try to connect SQL server to Oracle, no ETL required.
+52- Celebrity
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].[Tab1] Script Date: 22/08/2017 11:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tab1](
[id] [uniqueidentifier] NOT NULL,
[Comment] [nvarchar](50) NULL,
[foreignID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab1] WITH CHECK ADD CONSTRAINT [FK_Tab1_Tab2] FOREIGN KEY([foreignID])
REFERENCES [dbo].[Tab2] ([id])
GO
ALTER TABLE [dbo].[Tab1] CHECK CONSTRAINT [FK_Tab1_Tab2]
GO