[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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