Automating SQLCMD Statements

Recently, I wanted to automate the process of loading monthly csv data files into a SQL table. I spent some time researching the syntax of SQLCMD as well as reading multiple different posts on the subject. After some trial and error, I was able to get the automation functioning properly. Here’s a quick summary.

SQL Statement

Here’s the syntax for the SQL query I was using to upload the monthly files:

csv_input.txt

DATABASE NAME, TABLE, & DATA_FILE_LOCATION.csv are query specific. For my instance I used the following:

• DATABASE NAME: HYP_TEST
  
• TABLE: HYP_STG_AP_DATA
• DATA_FILE_LOCATION.csv: ‘G:\Data\AP\AP Aging FY14\ AP Aging Jan FY14.csv’

Open notepad and paste the query into the blank sheet. Next, save this file as a Microsoft SQL Server Query (.sql) file:

SQLCMD Batch File

To automate this process, a batch file will need to be created that calls the L_AP.sql file. Here’s the syntax to be used in the batch file:

SQLCMD -S server -U login_id –P password –i input_file -o output_file

Notes on the syntax:

For more detailed information on the SQLCMD syntax – https://msdn.microsoft.com/en-us/library/ms162773.aspx

Once you have tested SQLCMD in the command prompt to verify that it runs, paste it into notepad and save the file as a Windows Batch (.bat) file:

I also created the ‘Logs’ folder, which is where SQLCMD will write the output file (as specified after –o in the SQLCMD command line). The output file will be created as .txt:

At this point, the process should be good to go and you can replicate this setup for other SQL queries using sqlcmd.

As a result of this exercise, a years’ worth of data can be loaded all at once rather than having to upload the monthly data files one at a time. This saved a good amount of time that had previously been spent manually uploading monthly files during the incremental process of data validation.