RXNorm Database Build Process

The following database script will be executed based on a table by step by step.

--- MRCOLS Table Creation
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRCOLS') DROP TABLE MRCOLS;
CREATE TABLE MRCOLS (
    COL varchar(40),
    DES varchar(200),
    REF varchar(40),
    MIN int,
    AV  numeric(5,2),
    MAX int,
    FIL varchar(50),
    DTY varchar(40)
);

--- MRCOLS Table Data Insertion
BULK INSERT MRCOLS FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRCOLS.RRF'
 WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
-------------------------------------------------------------------------------------------------
--- MRCONSO Table Creation --- Truncation ERROR / Chnaged CODE size defult 100 to 300 -- dont know extact error but it fixes the propbelm
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRCONSO') DROP TABLE MRCONSO;
CREATE TABLE MRCONSO (
    CUI char(8) NOT NULL,
    LAT char(3) NOT NULL,
    TS  char(1) NOT NULL,
    LUI varchar(10) NOT NULL,
    STT varchar(3) NOT NULL,
    SUI varchar(10) NOT NULL,
    ISPREF  char(1) NOT NULL,
    AUI varchar(9) NOT NULL,
    SAUI    varchar(50),
    SCUI    varchar(100),
    SDUI    varchar(100),
    SAB varchar(40) NOT NULL,
    TTY varchar(40) NOT NULL,
    CODE    varchar(300) NOT NULL,
    STR varchar(max) NOT NULL,
    SRL int NOT NULL,
    SUPPRESS    char(1) NOT NULL,
    CVF int
)
ALTER TABLE MRCONSO ADD CONSTRAINT X_MRCONSO_PK PRIMARY KEY(AUI)

CREATE NONCLUSTERED INDEX X_MRCONSO_CUI ON MRCONSO(CUI)
CREATE NONCLUSTERED INDEX X_MRCONSO_SUI ON MRCONSO(SUI)
CREATE NONCLUSTERED INDEX X_MRCONSO_LUI ON MRCONSO(LUI)
CREATE NONCLUSTERED INDEX X_MRCONSO_CODE ON MRCONSO(CODE)
CREATE NONCLUSTERED INDEX X_MRCONSO_SAB_TTY ON MRCONSO(SAB,TTY)
CREATE NONCLUSTERED INDEX X_MRCONSO_SCUI ON MRCONSO(SCUI)
CREATE NONCLUSTERED INDEX X_MRCONSO_SDUI ON MRCONSO(SDUI)
--CREATE NONCLUSTERED INDEX X_MRCONSO_STR ON MRCONSO(STR(255));--variable length of indexing key value is max of 900 bytes but the table column name(key)'s >than 2000 bytes

BULK INSERT MRCONSO FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRCONSO.RRF'
 WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
------------------------------------------------------------------------------------------------
--- MRCUI Table Creation
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRCUI') DROP TABLE MRCUI;

CREATE TABLE MRCUI (
    CUI1    char(8) NOT NULL,
    VER varchar(10) NOT NULL,
    REL varchar(4) NOT NULL,
    RELA    varchar(100),
    MAPREASON   Varchar(MAX),
    CUI2    char(8),
    MAPIN   char(1)
) 

CREATE NONCLUSTERED INDEX X_MRCUI_CUI2 ON MRCUI(CUI2)

--- MRCUI Table Data Insertion
BULK INSERT MRCUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRCUI.RRF'
 WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

-----------------------------------------------------------------------------------

--- MRCXT Table Creation -- No data/file found
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRCXT') DROP TABLE MRCXT;

CREATE TABLE MRCXT (
    CUI char(8),
    SUI varchar(10),
    AUI varchar(9),
    SAB varchar(40),
    CODE    varchar(100),
    CXN int ,
    CXL char(3),
    RANK    int ,
    CXS varchar(max),
    CUI2    char(8),
    AUI2    varchar(9),
    HCD varchar(100),
    RELA    varchar(100),
    XC  varchar(1),
    CVF int 
)

CREATE NONCLUSTERED INDEX X_MRCXT_CUI ON MRCXT(CUI);
CREATE NONCLUSTERED INDEX X_MRCXT_AUI ON MRCXT(AUI);
CREATE NONCLUSTERED INDEX X_MRCXT_SAB ON MRCXT(SAB);

--- MRCXT Table Data Insertion
BULK INSERT MRCXT FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRCXT.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

---------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRDEF') DROP TABLE MRDEF;
CREATE TABLE MRDEF (
    CUI char(8) NOT NULL,
    AUI varchar(9) NOT NULL,
    ATUI    varchar(11) NOT NULL,
    SATUI   varchar(50),
    SAB varchar(40) NOT NULL,
    DEF varchar(MAX) NOT NULL,
    SUPPRESS    char(1) NOT NULL,
    CVF int
)

ALTER TABLE MRDEF ADD CONSTRAINT X_MRDEF_PK PRIMARY KEY(ATUI);
CREATE NONCLUSTERED INDEX X_MRDEF_CUI ON MRDEF(CUI);
CREATE NONCLUSTERED INDEX X_MRDEF_AUI ON MRDEF(AUI);
CREATE NONCLUSTERED INDEX X_MRDEF_SAB ON MRDEF(SAB);

--- MRDEF Table Data Insertion
BULK INSERT MRDEF FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRDEF.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRDOC') DROP TABLE MRDOC;
CREATE TABLE MRDOC (
    DOCKEY  varchar(50) NOT NULL,
    VALUE   varchar(200),
    TYPE    varchar(50) NOT NULL,
    EXPL    varchar(MAX)
)

--- MRDOC Table Data Insertion
BULK INSERT MRDOC FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRDOC.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

------------------------------------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRFILES') DROP TABLE MRFILES;
CREATE TABLE MRFILES (
    FIL varchar(50),
    DES varchar(200),
    FMT varchar(max),
    CLS int,
    RWS int,
    BTS bigint
)
--- MRFILES Table Data Insertion
BULK INSERT MRFILES FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRFILES.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
------------------------------------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRHIER') DROP TABLE MRHIER;
CREATE TABLE MRHIER (
    CUI char(8) NOT NULL,
    AUI varchar(9) NOT NULL,
    CXN int NOT NULL,
    PAUI varchar(10),
    SAB varchar(40) NOT NULL,
    RELA varchar(100),
    PTR varchar(max),
    HCD varchar(100),
    CVF int 
)

CREATE NONCLUSTERED INDEX X_MRHIER_CUI ON MRHIER(CUI);
CREATE NONCLUSTERED INDEX X_MRHIER_AUI ON MRHIER(AUI);
CREATE NONCLUSTERED INDEX X_MRHIER_SAB ON MRHIER(SAB);
CREATE NONCLUSTERED INDEX X_MRHIER_PAUI ON MRHIER(PAUI); 
--CREATE NONCLUSTERED INDEX X_MRHIER_PTR ON MRHIER(PTR(255));--indexing key value is max of 900 bytes but the table column name(key)'s >than 2000

BULK INSERT MRHIER FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRHIER.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

------------------------------------------------------------------------------------------------------


IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRHIST') DROP TABLE MRHIST;
CREATE TABLE MRHIST (
    CUI char(8),
    SOURCEUI    varchar(100),
    SAB varchar(40),
    SVER    varchar(40),
    CHANGETYPE  varchar(Max),
    CHANGEKEY   varchar(Max),
    CHANGEVAL   varchar(Max),
    REASON  varchar(Max),
    CVF int 
) 

CREATE NONCLUSTERED INDEX X_MRHIST_CUI ON MRHIST(CUI);
CREATE NONCLUSTERED INDEX X_MRHIST_SOURCEUI ON MRHIST(SOURCEUI);
CREATE NONCLUSTERED INDEX X_MRHIST_SAB ON MRHIST(SAB);

BULK INSERT MRHIST FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRHIST.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRMAP') DROP TABLE MRMAP;
CREATE TABLE MRMAP (
    MAPSETCUI   char(8) NOT NULL,
    MAPSETSAB   varchar(40) NOT NULL,
    MAPSUBSETID varchar(10),
    MAPRANK int ,
    MAPID   varchar(50) NOT NULL,
    MAPSID  varchar(50),
    FROMID  varchar(50) NOT NULL,
    FROMSID varchar(50),
    FROMEXPR    varchar(MAX) NOT NULL,
    FROMTYPE    varchar(50) NOT NULL,
    FROMRULE    varchar(MAX),
    FROMRES varchar(MAX),
    REL varchar(4) NOT NULL,
    RELA    varchar(100),
    TOID    varchar(50),
    TOSID   varchar(50),
    TOEXPR  varchar(MAX),
    TOTYPE  varchar(50),
    TORULE  varchar(MAX),
    TORES   varchar(MAX),
    MAPRULE varchar(MAX),
    MAPRES  varchar(MAX),
    MAPTYPE varchar(50),
    MAPATN  varchar(100),
    MAPATV  varchar(MAX),
    CVF int
)

CREATE NONCLUSTERED INDEX X_MRMAP_MAPSETCUI ON MRMAP(MAPSETCUI);

BULK INSERT MRMAP FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRMAP.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 


----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRRANK') DROP TABLE MRRANK;

CREATE TABLE MRRANK (
    RANK    int  NOT NULL,
    SAB varchar(40) NOT NULL,
    TTY varchar(40) NOT NULL,
    SUPPRESS    char(1) NOT NULL
)

ALTER TABLE MRRANK ADD CONSTRAINT X_MRRANK_PK  PRIMARY KEY(SAB,TTY);

BULK INSERT MRRANK FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRRANK.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRREL') DROP TABLE MRREL;

CREATE TABLE MRREL (
    CUI1    char(8) NOT NULL,
    AUI1    varchar(9),
    STYPE1  varchar(50) NOT NULL,
    REL varchar(4) NOT NULL,
    CUI2    char(8) NOT NULL,
    AUI2    varchar(9),
    STYPE2  varchar(50) NOT NULL,
    RELA    varchar(100),
    RUI varchar(10) NOT NULL,
    SRUI    varchar(50),
    SAB varchar(40) NOT NULL,
    SL  varchar(40) NOT NULL,
    RG  varchar(10),
    DIR varchar(1),
    SUPPRESS    char(1) NOT NULL,
    CVF int 
)

ALTER TABLE MRREL ADD CONSTRAINT X_MRREL_PK  PRIMARY KEY(RUI);

CREATE NONCLUSTERED INDEX X_MRREL_CUI1 ON MRREL(CUI1);
CREATE NONCLUSTERED INDEX X_MRREL_AUI1 ON MRREL(AUI1);
CREATE NONCLUSTERED INDEX X_MRREL_CUI2 ON MRREL(CUI2);
CREATE NONCLUSTERED INDEX X_MRREL_AUI2 ON MRREL(AUI2);
CREATE NONCLUSTERED INDEX X_MRREL_SAB ON MRREL(SAB);    

BULK INSERT MRREL FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRREL.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 


----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRSAB') DROP TABLE MRSAB;

CREATE TABLE MRSAB (
    VCUI    char(8),
    RCUI    char(8),
    VSAB    varchar(40) NOT NULL,
    RSAB    varchar(40) NOT NULL,
    SON varchar(MAX) NOT NULL,
    SF  varchar(40) NOT NULL,
    SVER    varchar(40),
    VSTART  char(8),
    VEND    char(8),
    IMETA   varchar(10) NOT NULL,
    RMETA   varchar(10),
    SLC varchar(MAX),
    SCC varchar(MAX),
    SRL int  NOT NULL,
    TFR int ,
    CFR int ,
    CXTY    varchar(50),
    TTYL    varchar(400),
    ATNL    text,
    LAT char(3),
    CENC    varchar(40) NOT NULL,
    CURVER  char(1) NOT NULL,
    SABIN   char(1) NOT NULL,
    SSN varchar(MAX) NOT NULL,
    SCIT    varchar(MAX) NOT NULL
) 

ALTER TABLE MRSAB ADD CONSTRAINT X_MRSAB_PK  PRIMARY KEY(VSAB);
CREATE NONCLUSTERED INDEX X_MRSAB_RSAB ON MRSAB(RSAB);

BULK INSERT MRSAB FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRSAB.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 


----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRSAT') DROP TABLE MRSAT;

CREATE TABLE MRSAT (
    CUI char(8) NOT NULL,
    LUI varchar(10),
    SUI varchar(10),
    METAUI  varchar(100),
    STYPE   varchar(50) NOT NULL,
    CODE    varchar(100),
    ATUI    varchar(11) NOT NULL,
    SATUI   varchar(50),
    ATN varchar(100) NOT NULL,
    SAB varchar(40) NOT NULL,
    ATV varchar(max),
    SUPPRESS    char(1) NOT NULL,
    CVF int 
)

ALTER TABLE MRSAT ADD CONSTRAINT X_MRSAT_PK  PRIMARY KEY(ATUI);

CREATE NONCLUSTERED INDEX X_MRSAT_CUI ON MRSAT(CUI);
CREATE NONCLUSTERED INDEX X_MRSAT_METAUI ON MRSAT(METAUI);
CREATE NONCLUSTERED INDEX X_MRSAT_SAB ON MRSAT(SAB);
CREATE NONCLUSTERED INDEX X_MRSAT_ATN ON MRSAT(ATN);

BULK INSERT MRSAT FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRSAT.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRSMAP') DROP TABLE MRSMAP;

CREATE TABLE MRSMAP (
    MAPSETCUI   char(8) NOT NULL,
    MAPSETSAB   varchar(40) NOT NULL,
    MAPID   varchar(50) NOT NULL,
    MAPSID  varchar(50),
    FROMEXPR    varchar(max) NOT NULL,
    FROMTYPE    varchar(50) NOT NULL,
    REL varchar(4) NOT NULL,
    RELA    varchar(100),
    TOEXPR  varchar(max),
    TOTYPE  varchar(50),
    CVF int 
)
BULK INSERT MRSMAP FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRSMAP.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRSTY') DROP TABLE MRSTY;

CREATE TABLE MRSTY (
    CUI char(8) NOT NULL,
    TUI char(4) NOT NULL,
    STN varchar(100) NOT NULL,
    STY varchar(50) NOT NULL,
    ATUI    varchar(11) NOT NULL,
    CVF int 
) 

ALTER TABLE MRSTY ADD CONSTRAINT X_MRSTY_PK  PRIMARY KEY(ATUI);

CREATE NONCLUSTERED INDEX X_MRSTY_CUI ON MRSTY(CUI);
CREATE NONCLUSTERED INDEX X_MRSTY_STY ON MRSTY(STY);

BULK INSERT MRSTY FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRSTY.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------


IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXNS_ENG') DROP TABLE MRXNS_ENG;

CREATE TABLE MRXNS_ENG (
    LAT char(3) NOT NULL,
    NSTR    varchar(max) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

--CREATE INDEX X_MRXNS_ENG_NSTR ON MRXNS_ENG(NSTR(255));--indexing key value is max of 900 bytes but the table column name(key)'s >than 2000

BULK INSERT MRXNS_ENG FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXNS_ENG.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXNW_ENG') DROP TABLE MRXNW_ENG;
CREATE TABLE MRXNW_ENG (
    LAT char(3) NOT NULL,
    NWD varchar(100) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXNW_ENG_NWD ON MRXNW_ENG(NWD);

BULK INSERT MRXNW_ENG FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXNW_ENG.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRAUI') DROP TABLE MRAUI;
CREATE TABLE MRAUI (
    AUI1    varchar(9) NOT NULL,
    CUI1    char(8) NOT NULL,
    VER varchar(10) NOT NULL,
    REL varchar(4),
    RELA    varchar(100),
    MAPREASON   varchar(max) NOT NULL,
    AUI2    varchar(9) NOT NULL,
    CUI2    char(8) NOT NULL,
    MAPIN   char(1) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRAUI_CUI2 ON MRAUI(CUI2);

BULK INSERT MRAUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRAUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
-- select top 10 * from MRAUI
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_BAQ') DROP TABLE MRXW_BAQ;
CREATE TABLE MRXW_BAQ (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_BAQ_WD ON MRXW_BAQ(WD);

BULK INSERT MRXW_BAQ FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_BAQ.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
--Current Status: Running
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_CHI') DROP TABLE MRXW_CHI;
CREATE TABLE MRXW_CHI (
    LAT char(3) NOT NULL,
    WD  varchar(2000) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
) 

CREATE NONCLUSTERED INDEX X_MRXW_CHI_WD ON MRXW_CHI(WD);

BULK INSERT MRXW_CHI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_CHI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_CZE') DROP TABLE MRXW_CZE;
CREATE TABLE MRXW_CZE (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_CZE_WD ON MRXW_CZE(WD);

BULK INSERT MRXW_CZE FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_CZE.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_DAN') DROP TABLE MRXW_DAN;
CREATE TABLE MRXW_DAN (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_DAN_WD ON MRXW_DAN(WD);

BULK INSERT MRXW_DAN FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_DAN.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_DUT') DROP TABLE MRXW_DUT;
CREATE TABLE MRXW_DUT (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_DUT_WD ON MRXW_DUT(WD);

BULK INSERT MRXW_DUT FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_DUT.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_ENG') DROP TABLE MRXW_ENG;
CREATE TABLE MRXW_ENG (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_ENG_WD ON MRXW_ENG(WD);

BULK INSERT MRXW_ENG FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_ENG.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_EST') DROP TABLE MRXW_EST;
CREATE TABLE MRXW_EST (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_EST_WD ON MRXW_EST(WD);

BULK INSERT MRXW_EST FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_EST.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_FIN') DROP TABLE MRXW_FIN;
CREATE TABLE MRXW_FIN (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_FIN_WD ON MRXW_FIN(WD);

BULK INSERT MRXW_FIN FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_FIN.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_FRE') DROP TABLE MRXW_FRE;
CREATE TABLE MRXW_FRE (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_FRE_WD ON MRXW_FRE(WD);

BULK INSERT MRXW_FRE FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_FRE.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_GER') DROP TABLE MRXW_GER;
CREATE TABLE MRXW_GER (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_GER_WD ON MRXW_GER(WD);

BULK INSERT MRXW_GER FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_GER.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_GRE') DROP TABLE MRXW_GRE;
CREATE TABLE MRXW_GRE (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_GRE_WD ON MRXW_GRE(WD);

BULK INSERT MRXW_GRE FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_GRE.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 


----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_HEB') DROP TABLE MRXW_HEB;
CREATE TABLE MRXW_HEB (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_HEB_WD ON MRXW_HEB(WD);

BULK INSERT MRXW_HEB FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_HEB.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 


----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_HUN') DROP TABLE MRXW_HUN;
CREATE TABLE MRXW_HUN (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_HUN_WD ON MRXW_HUN(WD);

BULK INSERT MRXW_HUN FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_HUN.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_ITA') DROP TABLE MRXW_ITA;
CREATE TABLE MRXW_ITA (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_ITA_WD ON MRXW_ITA(WD);

BULK INSERT MRXW_ITA FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_ITA.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 
----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_JPN') DROP TABLE MRXW_JPN;
CREATE TABLE MRXW_JPN (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

--CREATE NONCLUSTERED INDEX X_MRXW_JPN_WD ON MRXW_JPN(WD(255));--indexing key value is max of 900 bytes but the following table column name(key)'s >than 2000

BULK INSERT MRXW_JPN FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_JPN.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_KOR') DROP TABLE MRXW_KOR;
CREATE TABLE MRXW_KOR (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)
--CREATE NONCLUSTERED INDEX X_MRXW_KOR_WD ON MRXW_KOR(WD(255));--indexing key value is max of 900 bytes but the following table column name(key)'s >than 2000
BULK INSERT MRXW_KOR FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_KOR.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_LAV') DROP TABLE MRXW_LAV;
CREATE TABLE MRXW_LAV (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_LAV_WD ON MRXW_LAV(WD);

BULK INSERT MRXW_LAV FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_LAV.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_NOR') DROP TABLE MRXW_NOR;
CREATE TABLE MRXW_NOR (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_NOR_WD ON MRXW_NOR(WD);

BULK INSERT MRXW_NOR FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_NOR.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_POL') DROP TABLE MRXW_POL;
CREATE TABLE MRXW_POL (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_POL_WD ON MRXW_POL(WD);

BULK INSERT MRXW_POL FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_POL.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_POR') DROP TABLE MRXW_POR;
CREATE TABLE MRXW_POR (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_POR_WD ON MRXW_POR(WD);

BULK INSERT MRXW_POR FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_POR.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_RUS') DROP TABLE MRXW_RUS;
CREATE TABLE MRXW_RUS (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_RUS_WD ON MRXW_RUS(WD);

BULK INSERT MRXW_RUS FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_RUS.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_SCR') DROP TABLE MRXW_SCR;
CREATE TABLE MRXW_SCR (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_SCR_WD ON MRXW_SCR(WD);

BULK INSERT MRXW_SCR FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_SCR.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_SPA') DROP TABLE MRXW_SPA;
CREATE TABLE MRXW_SPA (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_SPA_WD ON MRXW_SPA(WD);

BULK INSERT MRXW_SPA FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_SPA.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_SWE') DROP TABLE MRXW_SWE;
CREATE TABLE MRXW_SWE (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_SWE_WD ON MRXW_SWE(WD);

BULK INSERT MRXW_SWE FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_SWE.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK); 

----------------------------------------------------------------------------

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MRXW_TUR') DROP TABLE MRXW_TUR;
CREATE TABLE MRXW_TUR (
    LAT char(3) NOT NULL,
    WD  varchar(200) NOT NULL,
    CUI char(8) NOT NULL,
    LUI varchar(10) NOT NULL,
    SUI varchar(10) NOT NULL
)

CREATE NONCLUSTERED INDEX X_MRXW_TUR_WD ON MRXW_TUR(WD);

BULK INSERT MRXW_TUR FROM 'D:\UMLS\UMLS_Installation\2018AA\META\MRXW_TUR.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'AMBIGSUI') DROP TABLE AMBIGSUI;
CREATE TABLE AMBIGSUI (
    SUI varchar(10) NOT NULL,
    CUI char(8) NOT NULL
);

CREATE NONCLUSTERED INDEX X_AMBIGSUI_SUI ON AMBIGSUI(SUI);

BULK INSERT AMBIGSUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\AMBIGSUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'AMBIGLUI') DROP TABLE AMBIGLUI;
CREATE TABLE AMBIGLUI (
    LUI varchar(10) NOT NULL,
    CUI char(8) NOT NULL
)

CREATE NONCLUSTERED INDEX X_AMBIGLUI_LUI ON AMBIGLUI(LUI);

BULK INSERT AMBIGLUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\AMBIGLUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'DELETEDCUI') DROP TABLE DELETEDCUI;
CREATE TABLE DELETEDCUI (
    LUI varchar(10) NOT NULL,
    CUI char(8) NOT NULL
)
BULK INSERT DELETEDCUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\CHANGE\DELETEDCUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);
----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'DELETEDLUI') DROP TABLE DELETEDLUI;
CREATE TABLE DELETEDLUI (
    PLUI    varchar(10) NOT NULL,
    PSTR    varchar(max) NOT NULL
)
BULK INSERT DELETEDLUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\CHANGE\DELETEDLUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'DELETEDSUI') DROP TABLE DELETEDSUI;
CREATE TABLE DELETEDSUI (
    PSUI    varchar(10) NOT NULL,
    LAT char(3) NOT NULL,
    PSTR    text NOT NULL
)
BULK INSERT DELETEDSUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\CHANGE\DELETEDSUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MERGEDCUI') DROP TABLE MERGEDCUI;
CREATE TABLE MERGEDCUI (
    PCUI    char(8) NOT NULL,
    CUI char(8) NOT NULL
)
BULK INSERT MERGEDCUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\CHANGE\MERGEDCUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);

----------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'MERGEDLUI') DROP TABLE MERGEDLUI;
CREATE TABLE MERGEDLUI (
    PLUI    varchar(10),
    LUI varchar(10)
)
BULK INSERT MERGEDLUI FROM 'D:\UMLS\UMLS_Installation\2018AA\META\CHANGE\MERGEDLUI.RRF'
WITH (FIRSTROW = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', TABLOCK);

-------------------------------- END OF THE EXECUTION --------------------------------------------