1

Is there an easy way to call one sql file from another?

We have 2 sql files - a.sql and b.sql for our products. Locally we create the db's A and B and then run a.sql an b.sql.

Now, there is another team that has been maintaining an sql file MasterFile.sql which is supposed to create all the dbs needed for this project and configure them. We now need to ensure that the contents of our sql files (a.sql and b.sql) are also called from this masterFile.sql.

Both a.sql and b.sql are subject to change in the near future and therefore we dont want to manually add the contents to MAsterFile.sql.

What is the best way to do this? I tried doing sqlcmd as well as source and :r a.sql, but none of them worked.

Omi
  • 976
  • 2
  • 20
  • 35

2 Answers2

1

How about using a batch file with 3 lines

sqlcmd /S servername /d databaseName /E /i "master.sql"
sqlcmd /S servername /d databaseName /E /i "a.sql"
sqlcmd /S servername /d databaseName /E /i "b.sql"
peterm
  • 91,357
  • 15
  • 148
  • 157
0

You can use xp_cmdshell and sqlcmd, as explained in this question:

Is it possible to execute a text file from SQL query?

Community
  • 1
  • 1
Optimax
  • 1,534
  • 2
  • 16
  • 23
  • I tried this out, but still get an error.."Invalid filename". – Omi May 28 '13 at 14:13
  • OK..good news and not so good news..Good news: it works..Not so good news: I have to provide an absolute path for the sql file..something like C:\myDB.sql...if I provide just the file name or some relative path, it does not work. – Omi May 28 '13 at 17:04
  • @techieChamp That's because the script is executing in SQL server's working directory (`C:\WINDOWS\system32` on my machine). If you build a relative path, it would have to be relative to that. – Optimax May 30 '13 at 18:34
  • @techieChamp You can use `XP_CMDSHELL "CD"` to get the current working directory inside the script. – Optimax May 30 '13 at 18:38