I have a folder in my project solution containing multiple .sql files which need to be executed. Now I can run them one by one but that will take me a while.
Is there any way to do this efficiently?
-
Which DBMS are you using? PostgreSQL? Oracle? Firebird? DB2?user330315– user3303152012-03-27 19:58:10 +00:00Commented Mar 27, 2012 at 19:58
-
tag added for what db i'm using.Yustme– Yustme2012-03-27 20:13:32 +00:00Commented Mar 27, 2012 at 20:13
Add a comment
|
4 Answers
I typically place the .sql files in a directory and run a batch file like this:
@ECHO OFF
for /f %%a in (ServerNames.txt) do (
setlocal
echo Accessing %%a...
for %%d in (*.sql) do sqlcmd -S%%a -E -i%%d
endlocal
)
This will execute all .sql scripts in the current directory using your windows authentication. "ServerNames.txt" holds all the names of each server you'd like to execute the .sql files on.
You may modify this to execute on just on server using the -S parameter
Additional parameters and information on sqlcmd can be found here
Comments
Here you have a way to do it
CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO
INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\SQL Scripts\*.sql"'
GO
DECLARE cFiles CURSOR LOCAL FOR
SELECT DISTINCT [SQLFileName]
FROM ##SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] != 'NULL'
ORDER BY [SQLFileName]
DECLARE @vFileName VARCHAR(200)
DECLARE @vSQLStmt VARCHAR(4000)
OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
-- The following SET command must be on a single line or else an error will be generated.
-- It is split in this script for readability purposes.
SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S Server Name -U User Name -P Password
-d Database Name -i "C:\SQL Scripts\' + @vFileName + '"'''
EXECUTE (@vSQLStmt)
FETCH NEXT FROM cFiles INTO @vFileName
END
CLOSE cFiles
DEALLOCATE cFiles
GO
DROP TABLE ##SQLFiles
GO
Comments
Heres one way using dotnet
protected void btn_Click(object sender, EventArgs e)
{
string strdbname=txtdbname.text;
string strCreatecmd = "create database " + strdbname + "";
SqlCommand cmd = new SqlCommand(strCreatecmd, con1);
con1.Open();
cmd.ExecuteNonQuery();
con1.Close();
// Code to execute sql script ie(create tables/storedprocedure/views on ms sqlserver)
//generatescript.sql is sql script generated and placed under Add_data folder in my application
FileInfo file = new FileInfo(Server.MapPath("App_Data\\generatescript.sql"));
string strscript = file.OpenText().ReadToEnd();
string strupdatescript = strscript.Replace("[databaseOldnameWhileSriptgenerate]", strdbname);
Server server = new Server(new ServerConnection(con1));
server.ConnectionContext.ExecuteNonQuery(strupdatescript);
con1.Close();
}
http://satindersinght.blogspot.in/2012/01/how-to-execute-sql-script-file-in.html
Comments
There are a lot of ways...
- Copy them all into one big text file
- create a batch file and use osql to run them
- create an applicaiton that reads the files and executes them. (For example, Using .NET you'd need a FielReader and some database logic depending on the RDBMS)
Probably more ideas would come to mind if I thought about it. What have you tried? What are your skills? Are you a developer? .NET, Java, VBScript, Perl, etc... It's a bit hard to give a specific answer with so little to go on.
