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
Page 1 / 1
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.
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.
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 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.
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