Link to home
Create AccountLog in
Avatar of dex11
dex11

asked on

convertion of oracle sql script to sql server script

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer