Solved

convertion of oracle sql script to sql server script

Posted on 2008-06-12
1
203 Views
Last Modified: 2013-12-19
Hello Experts,

I have created oracle database with the following sql script(attached) .What changes do you suggest to have the same exact database in ms sql server 2005 .I never used sql server before and i know we cannot have a sequence in sql server.please help.

Thank you.
create table TECHNOLOGIES

(

	TECH_ID         integer primary key,

	TECH_DESCR      varchar(20) not null,

        STATUS          VARCHAR(1)  default 'A',

        LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);

   
 

create table USER_DTLS(

	USER_ID		   integer primary key,

	USER_REPORT_TO_ID  integer ,

	LOGON_ID	   varchar(12) not null,

	PASSWORD  	   varchar(15) not null,

	USER_FIRST_NAME	   varchar(30) not null,

        USER_MIDDLE_NAME   varchar(30),

	USER_LAST_NAME     varchar(30) not null,

        PHONE_NO           varchar(18) not null,

        ALT_PHONE_NO       varchar(18),

        EMAIL_ID           varchar(35) not null,

        USER_LEVEL 	   NUMBER(2) not null,

	STATUS          VARCHAR(1)  default 'A',

        LAST_UPDATED_BY integer ,

        LAST_UPDATED_DATE DATE not null        

);
 
 

create table IMGRTN_STATUS

(

        IMGRTN_STATUS_ID        integer primary key,

	IMGRTN_STATUS_DESCR     varchar(20) not null,

        STATUS          VARCHAR(1)  default 'A',

        LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);
 
 

create table RESRC_STATUS

(

	RESRC_STATUS_ID        integer primary key,

	RESRC_STATUS_DESCR     varchar(20) not null,

        STATUS          VARCHAR(1)  default 'A',

        LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);
 

create table RESRC_COMP_DTLS(

	RESRC_COMP_ID       integer primary key,

	RESRC_COMP_NAME     varchar(30) not null,

	RESRC_COMP_TAXID    varchar(20),

	RESRC_COMP_ADDR1    varchar(40),

	RESRC_COMP_ADDR2    varchar(40),

	RESRC_COMP_PHONE1   varchar(18),

	RESRC_COMP_PHONE2   varchar(18),

	RESRC_COMP_FAX      varchar(12),       

        STATUS              varchar(1)  default 'A',

 	LAST_UPDATED_BY     varchar(12) not null,

        LAST_UPDATED_DATE   date not null

);
 
 

create table RESRC_COMP_CONTACTS

(

	RESRC_COMP_CONTACT_ID      integer unique,

	RESRC_COMP_ID              integer references RESRC_COMP_DTLS(RESRC_COMP_ID),

	RESRC_COMP_CONTACT_NAME    varchar(20) not null,

	RESRC_COMP_CONTACT_ADDR1   varchar(40),

	RESRC_COMP_CONTACT_ADDR2   varchar(40),

	RESRC_COMP_CONTACT_PHONE1  varchar(18) not null,

	RESRC_COMP_CONTACT_PHONE2  varchar(18),

	RESRC_COMP_CONTACT_FAX1    varchar(12),

        STATUS                     varchar(1)  default 'A',

 	LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null,

        primary key(RESRC_COMP_CONTACT_ID,RESRC_COMP_ID)

);
 

create table  VENDOR_DTLS

(

	VENDOR_ID         integer primary key,

	VENDOR_NAME       varchar(20) not null,

	VENDOR_ADDR1      varchar(40),

	VENDOR_ADDR2      varchar(40),

	VENDOR_PHONE1     varchar(18),

	VENDOR_PHONE2     varchar(18),

	VENDOR_FAX        varchar(12),

        STATUS            varchar(1)  default 'A',

 	LAST_UPDATED_BY   VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);
 
 
 

create table    VENDOR_CONTACTS

(

	VENDOR_CONTACT_ID    integer unique,

	VENDOR_ID            integer references VENDOR_DTLS(VENDOR_ID),

	VENDOR_CONTACT_NAME        varchar(20) not null,

	VENDOR_CONTACT_ADDR1      varchar(40),

	VENDOR_CONTACT_ADDR2      varchar(40),

	VENDOR_CONTACT_PHONE1     varchar(18) not null,

	VENDOR_CONTACT_PHONE2     varchar(18),

	VENDOR_CONTACT_FAX        varchar(12),

        STATUS                    varchar(1)  default 'A',

 	LAST_UPDATED_BY           VARCHAR(12) not null,

        LAST_UPDATED_DATE         DATE not null,

	primary key(VENDOR_CONTACT_ID,VENDOR_ID)

);
 

create table CLIENT_DTLS(

	CLIENT_ID		integer primary key,

	CLIENT_NAME		varchar(20) not null,

        STATUS                    varchar(1)  default 'A',

 	LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);
 

create table CLIENT_LOC_DTLS(

	CLIENT_LOC_ID		integer unique,

	CLIENT_ID		integer references CLIENT_DTLS(CLIENT_ID),

        CLIENT_LOC_NAME     	varchar(20) not null,

	CLIENT_CONTACT_NAME     varchar(20),

	CLIENT_ADDR1		varchar(40),

	CLIENT_ADDR2		varchar(40),

        CLIENT_PHONE            varchar(18),

	CLIENT_FAX              varchar(12),

 	STATUS                    varchar(1)  default 'A',

 	LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null,

        primary key(CLIENT_LOC_ID,CLIENT_ID)

);
 
 

create table RESRC_DTLS(

	RESRC_ID     integer  primary key,

	FIRST_NAME   varchar(30) not null,

	MIDDLE_NAME  varchar(30),

        LAST_NAME    varchar(30) not null,

	EMAIL_ID     varchar(35) not null,

        PHONE_NO     varchar(18) not null,

        ALT_PHONE_NO varchar(18), 

	FAX_NO       varchar(12),

	ADDR_LINE1   varchar(40),

	ADDR_LINE2   varchar(40),

 	CITY         varchar(20),

        STATE        varchar(20),

        COUNTRY      varchar(15),

        ZIP_CODE     varchar(6),

        RESRC_COMP_ID integer references  RESRC_COMP_DTLS(RESRC_COMP_ID) not null,

	RESRC_COMP_CONTACT_ID integer references RESRC_COMP_CONTACTS(RESRC_COMP_CONTACT_ID),

	TECH_ID       integer references TECHNOLOGIES(TECH_ID) not null,

	IMGRTN_STATUS_ID         integer references IMGRTN_STATUS(IMGRTN_STATUS_ID) not null,

	RESRC_STATUS_ID      integer references RESRC_STATUS(RESRC_STATUS_ID) not null,

	PRIMARY_SKILL_DESCR   varchar(100),

        RESRC_START_DATE date,

        RESRC_END_DATE date,

        EMERGENCY_CONTACT varchar(50),

	LAST_UPDATED_BY VARCHAR(12) not null,

        LAST_UPDATED_DATE DATE not null

);
 
 

create table  REQ_DTLS

(

	REQ_ID integer primary key,

        VENDOR_ID      integer references VENDOR_DTLS(VENDOR_ID),

        VENDOR_CONTACT_ID  integer references VENDOR_CONTACTS(VENDOR_CONTACT_ID),

	CLIENT_ID      integer references CLIENT_DTLS(CLIENT_ID),

	CLIENT_LOC_ID  integer references CLIENT_LOC_DTLS(CLIENT_LOC_ID),

	TECH_ID        integer references TECHNOLOGIES(TECH_ID),

	PRIMARY_SKILLS varchar(200),

	REQ_DESCR      varchar(1000),

	BILL_RATE      integer,

	NO_OF_POSITIONS integer not null,

	REQ_SUBMITTED_BY integer references USER_DTLS(USER_ID) not null, 

        REQ_OWNER integer  references USER_DTLS(USER_ID) not null,

	CREATE_DATE   Date not null,

        LAST_UPDATED_BY VARCHAR(12),

        LAST_UPDATED_DATE DATE not null

);
 
 

create table REQ_RESRC_DTLS(

	REQ_RESRC_DTLS_ID	integer unique,

	REQ_ID			integer references REQ_DTLS(REQ_ID),

        REQ_ASSIGN_TO		integer  references USER_DTLS(USER_ID) not null,

	RESRC_ID  	        integer references RESRC_DTLS(RESRC_ID),

	LAST_UPDATED_BY VARCHAR(12),

        LAST_UPDATED_DATE DATE not null,

        PRIMARY KEY(REQ_RESRC_DTLS_ID,REQ_ID)

);
 
 

create table REQ_NOTES(

        REQ_NOTE_ID             integer,

	REQ_RESRC_DTLS_ID	integer references REQ_RESRC_DTLS(REQ_RESRC_DTLS_ID),

	REQ_ID			integer references REQ_DTLS(REQ_ID),

	NOTE			varchar(200) not null,

	UPDATED_BY		VARCHAR(12) not null,

	UPDATED_DATE		Date not null,

        primary key( REQ_NOTE_ID,REQ_RESRC_DTLS_ID,REQ_ID)

);
 

create table RESUMES(

	RESRC_ID          integer references RESRC_DTLS(RESRC_ID),

	RESUME_ID         integer,

	TECH_ID	          integer references TECHNOLOGIES(TECH_ID),

        RESUME_FILE_NAME  varchar(50) not null,

        MIME_TYPE         varchar(20) not null,

        RESUME            blob not null,

        primary key(RESRC_ID,RESUME_ID,TECH_ID)	 

);
 
 

create sequence RESUME_ID_SEQ;

create sequence REQ_NOTE_ID_SEQ;

create sequence REQ_RESRC_DTLS_ID_SEQ;

create sequence REQ_ID_SEQ;

create sequence RESRC_ID_SEQ;

create sequence CLIENT_LOC_ID_SEQ;

create sequence CLIENT_ID_SEQ;

create sequence VENDOR_CONTACT_ID_SEQ;

create sequence VENDOR_ID_SEQ;

create sequence RESRC_COMP_CONTACT_ID_SEQ;

create sequence RESRC_COMP_ID_SEQ;

create sequence USER_ID_SEQ;
 

create table REQ_RESRC_STATUS

(

       REQ_STATUS_ID        integer primary key,

       REQ_STATUS_DESCR     varchar(20) not null,

       STATUS          VARCHAR(1)  default 'A',

       LAST_UPDATED_BY VARCHAR(12) not null,

       LAST_UPDATED_DATE DATE not null

);
 
 

ALTER TABLE REQ_RESRC_DTLS ADD REQ_STATUS_ID  INTEGER REFERENCES

 REQ_RESRC_STATUS(REQ_STATUS_ID);
 

alter table REQ_DTLS add POSITION varchar(25) not null;

Open in new window

0
Comment
Question by:dex11
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21771423
assuming that the TECH_ID field in the TECHNOLOGIES tables should be "auto_numbered":
create table TECHNOLOGIES
(
      TECH_ID         integer identity primary key,
      TECH_DESCR      varchar(20) not null,
        STATUS          VARCHAR(1)  default 'A',
        LAST_UPDATED_BY VARCHAR(12) not null,
        LAST_UPDATED_DATE DATETIME not null
); 

and you would NOT specify any value for TECH_ID during the insert, and find the value generated via
SELECT SCOPE_IDENTITY()

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now