Solved

How to run bunch of sql files on particular database

Posted on 2013-12-16
6
567 Views
Last Modified: 2013-12-17
I would like to run bunch of sql files on particular database using either batch file or C# program. How to achive this? I tried below batch file code but no luck.  The folder could be anywhere in my  computer. Please do assist.
for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Open in new window

and

c:\>for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f"

Open in new window

0
Comment
Question by:Easwaran Paramasivam
  • 3
  • 3
6 Comments
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 500 total points
ID: 39723873
@echo off
cls
rem Usage:  ScriptName.cmd server database "SQL folder"

rem validate passed-parameters
if [%1]==[] (
	echo missing server
	exit -1
)
if [%2]==[] (
	echo missing database
	exit -1
)
if [%3]==[] (
	echo missing folder
	exit -1
)

rem change to SQL folder
cd %3

rem process SQL files
for %%G in (*.sql) do (
	echo sqlcmd /S %1 /d %2 -E -i"%%G"
	sqlcmd /S %1 /d %2 -E -i"%%G"
	if ERRORLEVEL 1 exit -1
)

Open in new window

0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39723910
Thanks. Username password to the DB server are not required?
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39723921
In your example you used -E, which is windows-authentication.  If you have username/password then you could provide that, here is the syntax:
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Open in new window

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39724180
Thanks.  To execute in current folder I used below query. It works. How to include the subfolder of current folder here? Could you please suggest?

REM  - Get curernt directory

Pushd "%~dp0"

REM - Get all sql files and run specified DB

for %%G in (*.sql) do (
	sqlcmd /S server /U sa  /P pa@@w /d test -i"%%G"

	if ERRORLEVEL 1 exit -1
) 

pause

Open in new window

0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39724233
If you want to hard-code everything in the command-script instead of using passed-parameters then that's okay.  The for-loop may be enhanced to use relative/absolute folder notation:
for %%G in (blah\*.sql)

for %%G in (c:\temp\blah\*.sql)
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39725751
Excellent!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 57
Sql query with where clause 2 34
PolyServe for SQL server 13 32
Disable TLS1.0 on Win 2012 server 7 29
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question