CODEWITHSUNDEEP
sql-serveroracle-database
Yuwaz
Yuwaz

Reputation: 413

Migration DDL from Oracle to SQLServer

I would like to migrate DDL from Oracle to SQLServer. It was able to migrate to a certain extent. However, some items can not be migrated.

Oracle DDL:

CREATE TABLE ExampleTbl
(
    code CHAR(3) NOT NULL,
    code2 CHAR(3) NOT NULL,
    username VARCHAR2(255) NOT NULL,
    d  DATETIME

    CONSTRAINT PK_Example PRIMARY KEY (code, code2) USING INDEX
        PCTFREE 10
        INITRANS 2 -- <-?
        MAXTRANS 255 -- <-?
        TABLESPACE TBSP01
        STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) -- <-?
        LOGGING -- <-?
        ENABLE -- <-?
)
PCTFREE 10
MAXTRANS 255
TABLESPACE TBSP01
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) -- <-?
NOCACHE -- <-?
LOGGING
/
COMMENT ON TABLE ExampleTbl IS 'Table comment!'
/

SQLServer DDL: 

CREATE TABLE [dbo].[ExampleTbl](
  [code] [char](10) NOT NULL,
  [code2] [char](10) NOT NULL,
  [username] [varchar](255) NOT NULL,
  [d] [datetime] NULL,
  CONSTRAINT [PK_ExampleTbl] PRIMARY KEY CLUSTERED 
  (
    [code] ASC,
    [code2] ASC
  )
  WITH 
  (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90 -- FillFactor = 100 - Oracle.PCTFREE(10)
  ) ON [TBSP01] -- Oracle.TableSpace
) ON [TBSP01] -- Oracle.TableSpace

GO

EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Table comment!' , -- Oracle.Comment 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'ExampleTbl'
GO

Don't worry about column names.

How do I migrate these? INITRANS, MAXTRANS, STORAGE, LOGGING, ENABLE, NOCACHE.

And, are there any other problems?

Upvotes: 3

Views: 1550

Answers (1)

I A Khan
I A Khan

Reputation: 8839

CREATE TABLE Statement Converting CREATE TABLE statement keywords and clauses:

Oracle  SQL Server
1   ENABLE constraint attribute Removed
Storage and physical attributes:

Oracle  SQL Server
1   PCTFREE num Removed
2   PCTUSED num Removed
3   INITRANS num    Removed
4   MAXTRANS num    Removed
5   COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS    Removed
6   LOGGING | NOLOGGING Removed
7   SEGMENT CREATION IMMEDIATE | DEFERRED   Removed
8   TABLESPACE name ON name
9   LOB (column) STORE AS BASIC FILE (params)   Removed
10  PARALLEL num | NOPARALLEL   Removed
11  NOCACHE Removed
12  NOMONITORING    Removed
STORAGE clause:

Oracle  SQL Server
1   INITIAL num Removed
2   NEXT num    Removed
3   MINEXTENTS num  Removed
4   MAXEXTENTS num | UNLIMITED  Removed
5   PCTINCREASE num Removed
6   FREELISTS num   Removed
7   FREELIST GROUPS num Removed
8   BUFFER_POOL DEFAULT | KEEP | RECYCLE    Removed
9   FLASH_CACHE DEFAULT | KEEP | NONE   Removed
10  CELL_FLASH_CACHE DEFAULT | KEEP | NONE  Removed
LOB storage clause:

Oracle  SQL Server
1   TABLESPACE name Removed
2   DISABLE | ENABLE STORAGE IN ROW Removed
3   CHUNK num   Removed
4   NOCACHE Removed
5   LOGGING Removed

More Details http://www.sqlines.com/oracle-to-sql-server

Upvotes: 2

Related Questions