1

I have seen this question for windows: Run all SQL files in a directory

I was wondering how to do it for linux. To my knowledge .bat filetype is for windows. Anyone know a simple script for linux? I rarely use linux.

I have code to run them one at a time with

sqlcmd -S localhost -U SA -p myPassword -i myFile1.sql

(My sql files have which database to use). Just unsure how to make it run for all them since there are a lot.

Squashman
  • 13,649
  • 5
  • 27
  • 36
Lain
  • 2,166
  • 4
  • 23
  • 47

2 Answers2

2

A very simplistic sh script file might contain:

#!/bin/sh

#
# loop over the result of 'ls -1 *.sql'
#     'ls -1' sorts the file names based on the current locale 
#     and presents them in a single column
for i in `/bin/ls -1 *.sql`; do 
    sqlcmd -S localhost -U SA -p myPassword -i $i
done

If there is a specific order to the sql files then you would need to name them in a way that sorts into the correct order.

7 Reeds
  • 2,419
  • 3
  • 32
  • 64
  • Do I just save this as like myScript.sh in the same directory as the .SQL files and run it via terminal? – Lain Jun 11 '18 at 18:04
  • yes -- you save it to a file in that dir and then either: 1) `/bin/sh myScript.sh` or 2) ` chmod 754 myScript.sh; ./myScript.sh`. The first version uses the `sh` interpreter to run the script file and the second version makes the script file executable. – 7 Reeds Jun 11 '18 at 18:12
  • Thats what I did. However I got "Sqlcmd: 'myPassword': unexpected argument. Argument Value as to be one. " a crap ton of times (Assume for every file). – Lain Jun 11 '18 at 18:18
  • My first thought is: if your password has spaces or special characters in it make sure you surround it with single quotes. You could run into similar issues if your sql files have spaces or "shell special" characters in the names – 7 Reeds Jun 11 '18 at 18:24
  • password is literally myPassword haha, its just for testing. The files might be the problem. they are named dbo.something perhaps the . is the problem. Is there a way I can treat their names as string if that is the case? – Lain Jun 11 '18 at 18:27
  • periods in names are not an issue. Spaces, some shell meta-characters and other "invisible" characters can be headaches. If you have a truly huge number of files then you might have to investigate `xargs`... but that is getting out of the scope of "simplistic" script – 7 Reeds Jun 11 '18 at 18:29
  • Yep, gave the thumbs up for the help. I shall look into why it doesn't work – Lain Jun 11 '18 at 18:34
  • 1
    Why would you write ``for i in `/bin/ls -1 *.sql`;`` when you could just write `for i in *.sql;` which is more efficient and works reliably for filenames containing whitespace. – Socowi Jun 11 '18 at 18:42
  • @Socowi: good catch. In that moment I was going for a speedy answer. I was not 100% positive as I wrote it that plain "*.sql" would sort correctly in every case -- it probably does. the "ls" is belt and suspenders. – 7 Reeds Jun 11 '18 at 20:01
0

find to the rescue:

find ./directory -maxdepth 1 -name *.sql -exec sqlcmd -S localhost -U SA -p myPassword -i {} \;
Lacobus
  • 1,590
  • 12
  • 20