SQLMDX - MS SQL Migrate - Sample Schema

create or replace schema HUMANRESOURCES COMMENT='MS SCHEMA ID = 5 scripted 2025-02-04T02:12:59Z BY MIGRATESQL.COM';

create or replace TABLE DEPARTMENT (
	DEPARTMENTID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 noorder,
	NAME VARCHAR(100) NOT NULL,
	GROUPNAME VARCHAR(100) NOT NULL,
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	unique (NAME),
	primary key (DEPARTMENTID)
)COMMENT='MS OBJECT ID = 1381579960 scripted 2025-02-04T02:13:13Z BY MIGRATESQL.COM';

create or replace TABLE EMPLOYEE (
	BUSINESSENTITYID NUMBER(38,0) NOT NULL,
	NATIONALIDNUMBER VARCHAR(30) NOT NULL,
	LOGINID VARCHAR(512) NOT NULL,
	ORGANIZATIONNODE VARCHAR(892),
	ORGANIZATIONLEVEL NUMBER(38,0),
	JOBTITLE VARCHAR(100) NOT NULL,
	BIRTHDATE DATE NOT NULL,
	MARITALSTATUS VARCHAR(2) NOT NULL,
	GENDER VARCHAR(2) NOT NULL,
	HIREDATE DATE NOT NULL,
	SALARIEDFLAG BOOLEAN NOT NULL,
	VACATIONHOURS NUMBER(38,0) NOT NULL,
	SICKLEAVEHOURS NUMBER(38,0) NOT NULL,
	CURRENTFLAG BOOLEAN NOT NULL,
	ROWGUID VARCHAR(36) NOT NULL,
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	unique (LOGINID),
	unique (NATIONALIDNUMBER),
	unique (ROWGUID),
	primary key (BUSINESSENTITYID)
)COMMENT='MS OBJECT ID = 1573580644 scripted 2025-02-04T02:13:14Z BY MIGRATESQL.COM';

create or replace TABLE EMPLOYEEDEPARTMENTHISTORY (
	BUSINESSENTITYID NUMBER(38,0) NOT NULL,
	DEPARTMENTID NUMBER(38,0) NOT NULL,
	SHIFTID NUMBER(38,0) NOT NULL,
	STARTDATE DATE NOT NULL,
	ENDDATE DATE,
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	primary key (BUSINESSENTITYID, DEPARTMENTID, SHIFTID, STARTDATE)
)COMMENT='MS OBJECT ID = 1781581385 scripted 2025-02-04T02:13:16Z BY MIGRATESQL.COM';

create or replace TABLE EMPLOYEEPAYHISTORY (
	BUSINESSENTITYID NUMBER(38,0) NOT NULL,
	RATECHANGEDATE TIMESTAMP_NTZ(9) NOT NULL,
	RATE NUMBER(38,0) NOT NULL,
	PAYFREQUENCY NUMBER(38,0) NOT NULL,
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	primary key (BUSINESSENTITYID, RATECHANGEDATE)
)COMMENT='MS OBJECT ID = 1829581556 scripted 2025-02-04T02:13:17Z BY MIGRATESQL.COM';

create or replace TABLE JOBCANDIDATE (
	JOBCANDIDATEID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 noorder,
	BUSINESSENTITYID NUMBER(38,0),
	RESUME VARCHAR(16777216),
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	primary key (JOBCANDIDATEID)
)COMMENT='MS OBJECT ID = 1925581898 scripted 2025-02-04T02:13:20Z BY MIGRATESQL.COM';

create or replace TABLE SHIFT (
	SHIFTID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 noorder,
	NAME VARCHAR(100) NOT NULL,
	STARTTIME TIME(9) NOT NULL,
	ENDTIME TIME(9) NOT NULL,
	MODIFIEDDATE TIMESTAMP_NTZ(9) NOT NULL,
	unique (NAME),
	unique (STARTTIME, ENDTIME),
	primary key (SHIFTID)
)COMMENT='MS OBJECT ID = 382624406 scripted 2025-02-04T02:13:11Z BY MIGRATESQL.COM';

create or replace view VEMPLOYEE(
	BUSINESSENTITYID,
	TITLE,
	FIRSTNAME,
	MIDDLENAME,
	LASTNAME,
	SUFFIX,
	JOBTITLE,
	PHONENUMBER,
	PHONENUMBERTYPE,
	EMAILADDRESS,
	EMAILPROMOTION,
	ADDRESSLINE1,
	ADDRESSLINE2,
	CITY,
	STATEPROVINCENAME,
	POSTALCODE,
	COUNTRYREGIONNAME,
	ADDITIONALCONTACTINFO
) as 
SELECT 
	e.BUSINESSENTITYID
	,p.TITLE
	,p.FIRSTNAME
	,p.MIDDLENAME
	,p.LASTNAME
	,p.SUFFIX
	,e.JOBTITLE  
	,pp.PHONENUMBER
	,pnt.NAME AS PHONENUMBERTYPE
	,ea.EMAILADDRESS
	,p.EMAILPROMOTION
	,a.ADDRESSLINE1
	,a.ADDRESSLINE2
	,a.CITY
	,sp.NAME AS STATEPROVINCENAME 
	,a.POSTALCODE
	,cr.NAME AS COUNTRYREGIONNAME 
	,p.ADDITIONALCONTACTINFO
FROM HUMANRESOURCES.EMPLOYEE e
	INNER JOIN PERSON.PERSON p
	ON p.BUSINESSENTITYID = e.BUSINESSENTITYID
	INNER JOIN PERSON.BUSINESSENTITYADDRESS bea 
	ON bea.BUSINESSENTITYID = e.BUSINESSENTITYID 
	INNER JOIN PERSON.ADDRESS a 
	ON a.ADDRESSID = bea.ADDRESSID
	INNER JOIN PERSON.STATEPROVINCE sp 
	ON sp.STATEPROVINCEID = a.STATEPROVINCEID
	INNER JOIN PERSON.COUNTRYREGION cr 
	ON cr.COUNTRYREGIONCODE = sp.COUNTRYREGIONCODE
	LEFT OUTER JOIN PERSON.PERSONPHONE pp
	ON pp.BUSINESSENTITYID = p.BUSINESSENTITYID
	LEFT OUTER JOIN PERSON.PHONENUMBERTYPE pnt
	ON pp.PHONENUMBERTYPEID = pnt.PHONENUMBERTYPEID
	LEFT OUTER JOIN PERSON.EMAILADDRESS ea
	ON p.BUSINESSENTITYID = ea.BUSINESSENTITYID;
create or replace view VEMPLOYEEDEPARTMENT(
	BUSINESSENTITYID,
	TITLE,
	FIRSTNAME,
	MIDDLENAME,
	LASTNAME,
	SUFFIX,
	JOBTITLE,
	DEPARTMENT,
	GROUPNAME,
	STARTDATE
) as 
SELECT 
	e.BUSINESSENTITYID 
	,p.TITLE 
	,p.FIRSTNAME 
	,p.MIDDLENAME 
	,p.LASTNAME 
	,p.SUFFIX 
	,e.JOBTITLE
	,d.NAME AS DEPARTMENT 
	,d.GROUPNAME 
	,edh.STARTDATE 
FROM HUMANRESOURCES.EMPLOYEE e
	INNER JOIN PERSON.PERSON p
	ON p.BUSINESSENTITYID = e.BUSINESSENTITYID
	INNER JOIN HUMANRESOURCES.EMPLOYEEDEPARTMENTHISTORY edh 
	ON e.BUSINESSENTITYID = edh.BUSINESSENTITYID 
	INNER JOIN HUMANRESOURCES.DEPARTMENT d 
	ON edh.DEPARTMENTID = d.DEPARTMENTID 
WHERE edh.ENDDATE IS NULL;
create or replace view VEMPLOYEEDEPARTMENTHISTORY(
	BUSINESSENTITYID,
	TITLE,
	FIRSTNAME,
	MIDDLENAME,
	LASTNAME,
	SUFFIX,
	SHIFT,
	DEPARTMENT,
	GROUPNAME,
	STARTDATE,
	ENDDATE
) as 
SELECT 
	e.BUSINESSENTITYID 
	,p.TITLE 
	,p.FIRSTNAME 
	,p.MIDDLENAME 
	,p.LASTNAME 
	,p.SUFFIX 
	,s.NAME AS SHIFT
	,d.NAME AS DEPARTMENT 
	,d.GROUPNAME 
	,edh.STARTDATE 
	,edh.ENDDATE
FROM HUMANRESOURCES.EMPLOYEE e
	INNER JOIN PERSON.PERSON p
	ON p.BUSINESSENTITYID = e.BUSINESSENTITYID
	INNER JOIN HUMANRESOURCES.EMPLOYEEDEPARTMENTHISTORY edh 
	ON e.BUSINESSENTITYID = edh.BUSINESSENTITYID 
	INNER JOIN HUMANRESOURCES.DEPARTMENT d 
	ON edh.DEPARTMENTID = d.DEPARTMENTID 
	INNER JOIN HUMANRESOURCES.SHIFT s
	ON s.SHIFTID = edh.SHIFTID;

create or replace view VJOBCANDIDATE(
	DESCRIPTION
) as 

SELECT 'UNABLE TO MIGRATE VIEW FROM MSSQL';
create or replace view VJOBCANDIDATEEDUCATION(
	DESCRIPTION
) as 

SELECT 'UNABLE TO MIGRATE VIEW FROM MSSQL';
create or replace view VJOBCANDIDATEEMPLOYMENT(
	DESCRIPTION
) as 
SELECT 'UNABLE TO MIGRATE VIEW FROM MSSQL';