Solved

How to run bunch of sql files on particular database

Posted on 2013-12-16
6
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

615 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