Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

How to run bunch of sql files on particular database

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 3
  • 3
2 Solutions
 
John_VidmarCommented:
@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
 
Easwaran ParamasivamAuthor Commented:
Thanks. Username password to the DB server are not required?
0
 
John_VidmarCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Easwaran ParamasivamAuthor Commented:
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
 
John_VidmarCommented:
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
 
Easwaran ParamasivamAuthor Commented:
Excellent!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now