DECLARE @path VARCHAR(max);
DECLARE @root VARCHAR(max);

---- ---------MODIFY QUERY PARAMETERS BELOW, Do not modify anything above this line-----------------------------------------
SET @root = 'C:\Truven\ETL03'
-- Enter a filepath on the workstation/computer where the Summary Table .txt files are stored. The filepath should be in ' '. This path must exist on the same workstation/computer on which the SQl Server database is installed.

USE 'Desired database name'
-- The Database into which you are loading summary table data, the database name should be in " " and should be the database name you just created with the "Create Summary Tables" query


---------------------Do not modify anything below this line---------------------------------------------------------------------------

--Create a temporary table that contains all table names to be imported
DECLARE @tables TABLE
(
ID INT IDENTITY(1,1),
TableName VARCHAR(50)
)
INSERT INTO @tables(TableName)
SELECT ('Age_Groups')
UNION ALL
SELECT ('Enrollment')
UNION ALL
SELECT ('Drug_Class')
UNION ALL
SELECT ('Generic_Name')
UNION ALL
SELECT ('HCPCS')
UNION ALL
SELECT ('ICD9_DIAGNOSIS')
UNION ALL
SELECT ('ICD9_DIAGNOSIS_4_DIGIT')
UNION ALL
SELECT ('ICD9_DIAGNOSIS_5_DIGIT')
UNION ALL
SELECT ('ICD9_PROCEDURE')
UNION ALL
SELECT ('ICD9_PROCEDURE_4_DIGIT')
UNION ALL
SELECT ('INCIDENT_DRUG_CLASS')
UNION ALL
SELECT ('INCIDENT_GENERIC_NAME')
UNION ALL
SELECT ('INCIDENT_ICD9_DIAGNOSIS')

-----------------------------------------------------------------------------------------
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(TableName) FROM @tables)

DECLARE @I INT
SET @I = 1

----- Execute loop
WHILE (@I <= @RowCount)
BEGIN
-- Declare variable that will contain table name
	DECLARE @table VARCHAR(50)

-- Determine the current table to modify
-- Execute all modifications (delete all rows + bulk import from file to table)
	SELECT @table = TableName from @tables where ID = @I
	SET @path = '"' + @root + '\' + @table + '.txt"'
	PRINT('Deleting all existing rows in ' + @table)
	EXEC('delete from dbo.' + @table)
	PRINT('Inserting rows into ' + @table + ' from ' + @path)
	EXEC('BULK INSERT dbo.' + @table + ' FROM ' + @path + ' WITH (FIELDTERMINATOR =''\t'', ROWTERMINATOR = ''0x0a'', FIRSTROW = 2)')

SET @I = @I + 1
END