Oracle data type
|
SQL Server data type
|
Alternatives
|
BFILE
|
VARBINARY(MAX)
|
Yes
|
BLOB
|
VARBINARY(MAX)
|
Yes
|
CHAR([1-2000])
|
CHAR([1-2000])
|
Yes
|
CLOB
|
VARCHAR(MAX)
|
Yes
|
DATE
|
DATETIME
|
Yes
|
FLOAT
|
FLOAT
|
No
|
FLOAT([1-53])
|
FLOAT([1-53])
|
No
|
FLOAT([54-126])
|
FLOAT
|
No
|
INT
|
NUMERIC(38)
|
Yes
|
INTERVAL
|
DATETIME
|
Yes
|
LONG
|
VARCHAR(MAX)
|
Yes
|
LONG RAW
|
IMAGE
|
Yes
|
NCHAR([1-1000])
|
NCHAR([1-1000])
|
No
|
NCLOB
|
NVARCHAR(MAX)
|
Yes
|
NUMBER
|
FLOAT
|
Yes
|
NUMBER([1-38])
|
NUMERIC([1-38])
|
No
|
NUMBER([0-38],[1-38])
|
NUMERIC([0-38],[1-38])
|
Yes
|
NVARCHAR2([1-2000])
|
NVARCHAR([1-2000])
|
No
|
RAW([1-2000])
|
VARBINARY([1-2000])
|
No
|
REAL
|
FLOAT
|
No
|
ROWID
|
CHAR(18)
|
No
|
TIMESTAMP
|
DATETIME
|
Yes
|
TIMESTAMP(0-7)
|
DATETIME
|
Yes
|
TIMESTAMP(8-9)
|
DATETIME
|
Yes
|
TIMESTAMP(0-7) WITH TIME ZONE
|
VARCHAR(37)
|
Yes
|
TIMESTAMP(8-9) WITH TIME ZONE
|
VARCHAR(37)
|
No
|
TIMESTAMP(0-7) WITH LOCAL TIME ZONE
|
VARCHAR(37)
|
Yes
|
TIMESTAMP(8-9) WITH LOCAL TIME ZONE
|
VARCHAR(37)
|
No
|
UROWID
|
CHAR(18)
|
No
|
VARCHAR2([1-4000])
|
VARCHAR([1-4000])
|
Yes
|
The DDL script is for Oracle database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | CREATE TABLE "AP_NTA"."DBM030FA" ( "STA_DATE" DATE, "ORG_TYPE" VARCHAR2(1 BYTE), "AGE" VARCHAR2(12 BYTE), "AGE_NAME" NVARCHAR2(30), "MASTER_AGE" VARCHAR2(12 BYTE), "USER_ID" VARCHAR2(20 BYTE), "UPDATE_DATE" DATE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DBM_DAT" ; REM INSERTING into AP_NTA.DBM030FA SET DEFINE OFF; Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','397000000A','高雄市政府','A00000000A','TEST',to_date('20-3月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A00000000A','行政院',null,'TEST',to_date('20-3月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','382000000A','新北市政府','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376580000A','新竹市政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A01000000A ','內政部','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'4','376446400A ','竹北市公所','376440000A','TEST',to_date('16-4月 -15','DD-MON-RR')); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376440000A','新竹縣政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR')); -------------------------------------------------------- -- DDL for Index SYS_C0072659 -------------------------------------------------------- CREATE UNIQUE INDEX "AP_NTA"."SYS_C0072659" ON "AP_NTA"."DBM030FA" ("STA_DATE", "ORG_TYPE", "AGE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DBM_DAT" ; -------------------------------------------------------- -- Constraints for Table DBM030FA -------------------------------------------------------- ALTER TABLE "AP_NTA"."DBM030FA" ADD PRIMARY KEY ("STA_DATE", "ORG_TYPE", "AGE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DBM_DAT" ENABLE; ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE_NAME" NOT NULL ENABLE); ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE" NOT NULL ENABLE); ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("ORG_TYPE" NOT NULL ENABLE); ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("STA_DATE" NOT NULL ENABLE); |
The following DDL script had been translated for Microsoft SQL Sever database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE TABLE "AP_NTA"."DBM030FA" ( "STA_DATE" DATETIME, "ORG_TYPE" VARCHAR(1), "AGE" VARCHAR(12), "AGE_NAME" NVARCHAR(30), "MASTER_AGE" VARCHAR(12), "USER_ID" VARCHAR(20), "UPDATE_DATE" DATETIME, CONSTRAINT DBM030FA_PK PRIMARY KEY (STA_DATE, ORG_TYPE, AGE) ) ; Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','397000000A','高雄市政府','A00000000A','TEST',CAST('2015-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A00000000A','行政院',null,'TEST',CAST('2014-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','382000000A','新北市政府','A00000000A','TEST',CAST('2014-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376580000A','新竹市政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A01000000A ','內政部','A00000000A','TEST',CAST('2014-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'4','376446400A ','竹北市公所','376440000A','TEST',CAST('2014-07-17' AS datetime)); Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376440000A','新竹縣政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime)); |
Reference
[1] https://msdn.microsoft.com/en-us/library/ms151817.aspx
No comments:
Post a Comment