Creating a SQL Server Summary Table Database

Owned by Zac Wyner (Unlicensed)
Jun 01, 2022
2 min read

By 10/1/2019 Microsoft Access will no longer be supported by PopMedNet for Summary Table queries. The instructions below explain how to create a SQL server summary table database from .txt files. These instructions assume you have have SQL server and text files that represent your summary table data.  The queries included in the below steps are provided to assist you in creating and maintaining a Summary Table Database in SQL Server. If you already have an ETL process in place you may continue to use your current process. 

Steps:

  1. Obtain the most recent ETL .txt files that represent your Summary Table data
    1. Users of the Sentinel network are sent a summary table creation package via PopMedNet (a SAS program) that generates these .txt files. One text file is generated for each Summary Table Database table
  2. Place the summary table files .txt files in a folder on the workstation/computer on which SQL Server is installed. Please note, SQL Server Management Studio, (or the SQL management program you use) might not be installed on the same machine as SQL server.
  3. Open SQL Server Management Studio (or the SQL management program you use).
  4. Execute this query: Create Summary Table Database
    1. This query will create a properly formatted and empty summary table database within your SQL Server environment. 
    2. Within the query there are placeholders where you must enter a desired database name. They are clearly indicated within the code.
    3. After executing the query, you may need to refresh the database list before the database will appear in your list of databases.
  5. After the empty database has been successfully created, execute this query: Import Summary Table Data
    1. There is a placeholder within this query where you must enter the filepath where your summary table .txt files are stored. If you do not enter the proper file path, you will get an error when executing the query. The placeholder is clearly indicated within the code.
    2. This query may take a few minutes to run depending on the amount of data being imported.
  6. If the query in step 5 is successful, you have successfully created a Summary Table Database in SQL Server.