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
+45- Evangelist
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.
+18- 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.
erik_jan wrote:
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.
+45- Evangelist
erik_jan wrote:
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.
+45- Evangelist
lenaatsafe wrote:
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
danilo_fme wrote:
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
lenaatsafe wrote:
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.
+45- Evangelist
hcode wrote:
I would try to connect SQL server to Oracle, no ETL required.
+45- Evangelist
lenaatsafe wrote:
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
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.