Avatar of ralph_rea
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?
Microsoft SQL ServerMicrosoft SQL Server 2005Oracle Database

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
Raja Jegan R

Replace all Varchar2 as Varchar and hope you are almost done.

Other than that there are no major changes in those scripts.
Sharath S

you can also remove the "byte" in the varchar decleration.
VARCHAR2(16 BYTE) should be varchar(16)
ASKER
ralph_rea

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
nkhelashvili

CREATE TABLE SITE
(
  NAME                VARCHAR(32)          NULL,
  SITE_ID             VARCHAR(16)        PRIMERY KEY CLUSTERED
);
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nkhelashvili

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]

Open in new window

ASKER
ralph_rea

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 '('.



Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
nkhelashvili

About foreign keys, rrjegan17 wrote above, review his last post :)
awking00

>>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.
awking00

See attached Oracle example.
Oracle-example.txt
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Raja Jegan R

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..
Raja Jegan R

I meant the awking00 script..
And I have provided the exact syntax earlier in my reply too with an example.