I am using command line in Windows 7 to read rows of variables from a CSV, pass those variables into a SQL command, and execute that SQL command for each row in the CSV. This works fine, I believe. Where I am running into trouble is when my variables contain spaces, such as strings that need to be input into my database.
My code looks like this:
Command line:
C:\Users\me>(sqlcmd -S npl-sql01 -d OnBaseTEST -i "C:\Users\me\myquery.sql" -v var1="\Policy Term\Policy\" var2= 'REPORT' var3= 'Commercial' )
Where var1, var2, and var3 are variables in the SQL query that get their values from a CSV.
SQL query:
INSERT INTO testdb (column1, column2, column3)
SELECT $(var1), $(var2), $(var3), od.value
FROM otherdatabase AS od
WHERE od.identifier = 24
When I wrap the variable np in single quotes, the command line chokes up on the space in the path. When I wrap it in double quotes, SQL appears to be treating it as a column name rather than a value to be put into a column (based off of this question). I have also tried wrapping with two sets of quotes, e.g. "'a string'" and '"a string"', with no luck. How should I be writing out this string with spaces so that both command line and SQL can understand it?
Edit: For some reason, removing the trailing variable var3 allows the script to work as intended when the string is wrapped with "'two types of quotes'"
INSERT INTO testdb (column1, column2, column3) SELECT '$(var1)', '$(var2)', '$(var3)', od.value FROM otherdatabase AS od WHERE od.identifier = 24