Need help with using sqlcmd in batch file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with using sqlcmd in batch file

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-26 : 15:54:01

Hi guys,

I'm writting a Windows batch file that will do the following:

1. Copy 2 CSV files from one location to another (admit.csv, disch.csv)
2. Truncate 2 SQL Server tables (ADMIT_PT, DISCH_PT)
3. Import 2 CSV files into their respective tables

I have figured out step #1 but I am stuck in figuring out the syntax for step #2 and #3. Here's what I have so far:

@echo off
xcopy "\\AnyServer\*.csv" "C:\Temp\" /y

I have done some research for the sqlcmd command but all of the examples online use sql script files that are called by the batch file.

Here's some of the information that will be required to put this together:

Server Name: MYSERVER
Instance Name: SQL01
Authentication: Windows Authentication
Database Name: MYDB

If somebody can get me started with #2 I may be able to take a shot at figuring out #3, but if you want to go all the way its a CSV file (commad delimited, obviously). There are 7 fields in total, which all have text qualifiers around them and one of the fields has last names separated from first names with a comma. i.e. "LAST NAME, FIRST NAME"

Your assistance will be greatly appreciated.
Thank you!!!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-26 : 18:33:44

@echo off
xcopy "\\AnyServer\*.csv" "C:\Temp\" /y

sqlcmd -E -s MyServer\SQL01 -i MyScript.sql

The MyScript.sql file would hold the two TRUNCATE statements

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-26 : 23:34:57

Thanks for the reply. However, I need the sql syntax in the actual batch file and not have to reference any external sql files.

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-27 : 10:07:56

I was able to figure out #2. The sulution is:

sqlcmd -S MYSERVER\SQL_01 -d MYDB -Q "TRUNCATE TABLE ADMIT_PT"

Now, I have to figure out #3.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-27 : 13:24:28

m'kay

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-27 : 21:09:30

Here's the solution for #3, in case anyone is interested. Trying to address the text qualifiers around each column value ("") and the commas within the field values was getting too complicated so I changed the source files to be pipe delimited (|) without the text qualifiers and it works like a beauty!

sqlcmd -S MYSERVER\SQL01 -d MYDB -Q "TRUNCATE TABLE ADMIT_PT"
sqlcmd -S MYSERVER\SQL01 -d MYDB -Q "TRUNCATE TABLE DISCH_PT"
sqlcmd -S MYSERVER\SQL01 -d MYDB -Q "BULK INSERT ADMIT_PT FROM 'C:\Temp\Admit.csv' WITH (FIELDTERMINATOR = '|', FIRSTROW = 2)"
sqlcmd -S MYSERVER\SQL01 -d MYDB -Q "BULK INSERT DISCH_PT FROM 'C:\Temp\Disch.csv' WITH (FIELDTERMINATOR = '|', FIRSTROW = 2)"

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources