ralph_rea
asked on
Script From Oracle To SQL Server
Hi,
I've this Oracle script:
-- create table SITE
CREATE TABLE SITE
(
NAME VARCHAR2(32 BYTE) DEFAULT NULL,
SITE_ID VARCHAR2(16 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE SITE ADD (
CONSTRAINT SITE_PK
PRIMARY KEY
(SITE_ID);
-- create table BL
CREATE TABLE BL
(
NAME VARCHAR2(25 BYTE) DEFAULT NULL,
SITE_ID VARCHAR2(16 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE BL ADD (
CONSTRAINT BL_PK
PRIMARY KEY
(BL_ID);
ALTER TABLE BL ADD (
CONSTRAINT BL_SITE_ID
FOREIGN KEY (SITE_ID)
REFERENCES SITE (SITE_ID));
-- create table FL
CREATE TABLE FL
(
NAME VARCHAR2(25 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL,
FL_ID VARCHAR2(4 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE FL ADD (
CONSTRAINT FL_PK
PRIMARY KEY
(BL_ID, FL_ID);
ALTER TABLE FL ADD (
CONSTRAINT FL_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
-- create table RM
CREATE TABLE RM
(
NAME VARCHAR2(16 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL,
FL_ID VARCHAR2(4 BYTE) DEFAULT NULL NOT NULL,
RM_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE RM ADD (
CONSTRAINT RM_PK
PRIMARY KEY
(BL_ID, FL_ID, RM_ID),
ALTER TABLE RM ADD (
CONSTRAINT RM_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_FL_ID
FOREIGN KEY (BL_ID, FL_ID)
REFERENCES FL (BL_ID,FL_ID));
I'd like to write the same script for SQL Server 2005
Have you any idea?
I've this Oracle script:
-- create table SITE
CREATE TABLE SITE
(
NAME VARCHAR2(32 BYTE) DEFAULT NULL,
SITE_ID VARCHAR2(16 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE SITE ADD (
CONSTRAINT SITE_PK
PRIMARY KEY
(SITE_ID);
-- create table BL
CREATE TABLE BL
(
NAME VARCHAR2(25 BYTE) DEFAULT NULL,
SITE_ID VARCHAR2(16 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE BL ADD (
CONSTRAINT BL_PK
PRIMARY KEY
(BL_ID);
ALTER TABLE BL ADD (
CONSTRAINT BL_SITE_ID
FOREIGN KEY (SITE_ID)
REFERENCES SITE (SITE_ID));
-- create table FL
CREATE TABLE FL
(
NAME VARCHAR2(25 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL,
FL_ID VARCHAR2(4 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE FL ADD (
CONSTRAINT FL_PK
PRIMARY KEY
(BL_ID, FL_ID);
ALTER TABLE FL ADD (
CONSTRAINT FL_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
-- create table RM
CREATE TABLE RM
(
NAME VARCHAR2(16 BYTE) DEFAULT NULL,
BL_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL,
FL_ID VARCHAR2(4 BYTE) DEFAULT NULL NOT NULL,
RM_ID VARCHAR2(8 BYTE) DEFAULT NULL NOT NULL
);
ALTER TABLE RM ADD (
CONSTRAINT RM_PK
PRIMARY KEY
(BL_ID, FL_ID, RM_ID),
ALTER TABLE RM ADD (
CONSTRAINT RM_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_FL_ID
FOREIGN KEY (BL_ID, FL_ID)
REFERENCES FL (BL_ID,FL_ID));
I'd like to write the same script for SQL Server 2005
Have you any idea?
Last Comment
you can also remove the "byte" in the varchar decleration.
VARCHAR2(16 BYTE) should be varchar(16)
VARCHAR2(16 BYTE) should be varchar(16)
ASKER
rrjegan17,
are you sure?
CREATE TABLE SITE
(
NAME VARCHAR(32) DEFAULT NULL,
SITE_ID VARCHAR(16) DEFAULT NULL NOT NULL
);
Command(s) completed successfully.
ALTER TABLE SITE ADD ( CONSTRAINT SITE_PK PRIMARY KEY (SITE_ID);
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Have someone any idea?
are you sure?
CREATE TABLE SITE
(
NAME VARCHAR(32) DEFAULT NULL,
SITE_ID VARCHAR(16) DEFAULT NULL NOT NULL
);
Command(s) completed successfully.
ALTER TABLE SITE ADD ( CONSTRAINT SITE_PK PRIMARY KEY (SITE_ID);
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Have someone any idea?
CREATE TABLE SITE
(
NAME VARCHAR(32) NULL,
SITE_ID VARCHAR(16) PRIMERY KEY CLUSTERED
);
(
NAME VARCHAR(32) NULL,
SITE_ID VARCHAR(16) PRIMERY KEY CLUSTERED
);
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Above what I wrote was if there is one field primery key, but if multiple, than:
CREATE TABLE dbo.Table_1
(
Field1 ntext NULL,
Field2 nchar(10) NOT NULL,
Field3 nchar(10) NOT NULL,
Field4 nchar(10) NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
Field2 ,
Field3 ,
Field4
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ASKER
ok, but with:
ALTER TABLE BL ADD (
CONSTRAINT BL_SITE_ID
FOREIGN KEY (SITE_ID)
REFERENCES SITE (SITE_ID));
ALTER TABLE FL ADD (
CONSTRAINT FL_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_FL_ID
FOREIGN KEY (BL_ID, FL_ID)
REFERENCES FL (BL_ID,FL_ID));
I get same error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
ALTER TABLE BL ADD (
CONSTRAINT BL_SITE_ID
FOREIGN KEY (SITE_ID)
REFERENCES SITE (SITE_ID));
ALTER TABLE FL ADD (
CONSTRAINT FL_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_BL_ID
FOREIGN KEY (BL_ID)
REFERENCES BL (BL_ID));
ALTER TABLE RM ADD (
CONSTRAINT RM_FL_ID
FOREIGN KEY (BL_ID, FL_ID)
REFERENCES FL (BL_ID,FL_ID));
I get same error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
About foreign keys, rrjegan17 wrote above, review his last post :)
>>ALTER TABLE SITE ADD ( CONSTRAINT SITE_PK PRIMARY KEY (SITE_ID);
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Have someone any idea?<<
You have two left parentheses and one right one. Remove the one between ADD and CONSTRAINT. This should have also created a syntax error when executed in Oracle.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Have someone any idea?<<
You have two left parentheses and one right one. Remove the one between ADD and CONSTRAINT. This should have also created a syntax error when executed in Oracle.
See attached Oracle example.
Oracle-example.txt
Oracle-example.txt
Ok.. In SQL Server, the syntax for Primary Key is as below
ALTER TABLE SITE ADD CONSTRAINT SITE_PK PRIMARY KEY (SITE_ID)
and Foreign Key syntax is as below
ALTER TABLE SITE ADD CONSTRAINT FK_sitetype FOREIGN KEY (site_type_id)
REFERENCES SITE_TYPE (SITE_TYPE_ID) ;
Anyhow you have successfully ran the script as per your example right..
ALTER TABLE SITE ADD CONSTRAINT SITE_PK PRIMARY KEY (SITE_ID)
and Foreign Key syntax is as below
ALTER TABLE SITE ADD CONSTRAINT FK_sitetype FOREIGN KEY (site_type_id)
REFERENCES SITE_TYPE (SITE_TYPE_ID) ;
Anyhow you have successfully ran the script as per your example right..
I meant the awking00 script..
And I have provided the exact syntax earlier in my reply too with an example.
And I have provided the exact syntax earlier in my reply too with an example.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Other than that there are no major changes in those scripts.