/************************************************************************************/
/* Unified Medical Language System - Microsoft SQL Server database load script */
/* */
/* Semantic Network Load Script */
/* Use this script on an empty database to create UMLS Semantic network tables and */
/* load them with data from files. You only have to specify the @DataFilePath */
/* variable to point to the NET folder of your subset. */
/* */
/* February 2011 - By Dr. Nader Elshehabi */
/* dr.nader.elshehabi@gmail.com */
/************************************************************************************/

/****** Declaring variables that will be used in loading data ******/
DECLARE @DataFilePath NVARCHAR(500);
DECLARE @AllTables NVARCHAR(500);
DECLARE @ChangeTables NVARCHAR(500);
DECLARE @CurrentFile NVARCHAR(500);
DECLARE @CurrentPosition INT;
DECLARE @CurrentTable NVARCHAR(500);
DECLARE @statement NVARCHAR(500);

/* Change this path to the root folder of your semantic network e.g. N'D:\UMLS\2010AA\NET' */
SET @DataFilePath = N'TODO: TYPE YOUR NET FOLDER PATH HERE'

/****** Comma separated list of table names which are mapped to RRF files in the path mentioned above ******/
SET @AllTables = N'SRDEF,SRFIL,SRFLD,SRSTR,SRSTRE1,SRSTRE2'

/****** Creating Tables Schema by DROP and CREATE ******/

/****** DROP tables if exist first ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srdef]') AND type in (N'U'))
DROP TABLE [dbo].[srdef]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srfil]') AND type in (N'U'))
DROP TABLE [dbo].[srfil]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srfld]') AND type in (N'U'))
DROP TABLE [dbo].[srfld]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstr]') AND type in (N'U'))
DROP TABLE [dbo].[srstr]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstre1]') AND type in (N'U'))
DROP TABLE [dbo].[srstre1]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstre2]') AND type in (N'U'))
DROP TABLE [dbo].[srstre2]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstre2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srstre2](
[STY1] [varchar](41) NOT NULL,
[RL] [varchar](23) NOT NULL,
[STY2] [varchar](41) NOT NULL
) ON [PRIMARY]
END

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstre1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srstre1](
[UI1] [char](4) NOT NULL,
[UI2] [char](4) NOT NULL,
[UI3] [char](4) NOT NULL
) ON [PRIMARY]
END

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srstr]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srstr](
[STY_RL1] [varchar](41) NOT NULL,
[RL] [varchar](23) NOT NULL,
[STY_RL2] [varchar](39) NULL,
[LS] [varchar](3) NOT NULL
) ON [PRIMARY]
END

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srfld]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srfld](
[COL] [varchar](3) NOT NULL,
[DES] [varchar](32) NOT NULL,
[REF] [varchar](3) NOT NULL,
[FIL] [varchar](19) NOT NULL
) ON [PRIMARY]
END

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srfil]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srfil](
[FIL] [varchar](7) NOT NULL,
[DES] [varchar](56) NOT NULL,
[FMT] [varchar](41) NOT NULL,
[CLS] [varchar](2) NOT NULL,
[RWS] [varchar](4) NOT NULL,
[BTS] [varchar](6) NOT NULL
) ON [PRIMARY]
END

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[srdef]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[srdef](
[RT] [varchar](3) NOT NULL,
[UI] [char](4) NOT NULL,
[STY_RL] [varchar](41) NOT NULL,
[STN_RTN] [varchar](14) NOT NULL,
[DEF] [text] NOT NULL,
[EX] [varchar](185) NULL,
[UN] [text] NULL,
[NH] [varchar](1) NULL,
[ABR] [varchar](4) NOT NULL,
[RIN] [varchar](23) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

SET ANSI_PADDING OFF

/****** Inserting data into tables ******/
if right(rtrim(@AllTables),1) <> ','
set @AllTables = @AllTables + ','
set @CurrentPosition = patindex('%,%' , @AllTables)
while @CurrentPosition <> 0
begin

set @CurrentTable = left(@AllTables, @CurrentPosition - 1);
set @AllTables = stuff(@AllTables, 1, @CurrentPosition, '');
set @CurrentPosition = patindex('%,%' , @AllTables);
SET @CurrentFile = @DataFilePath + N'\' + @CurrentTable;
SET @statement = N'
BULK INSERT [' + @CurrentTable + N'] FROM ''' + @CurrentFile + N'''
WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''|' + CHAR(10) + ''')
';

PRINT('Executing command ' + @statement);

EXEC dbo.sp_executesql @statement;
END