How To Run Sql Script Files from Unix Prompt?

I had a requirement where in I had to update huge text content in the database. While writing a groovy script had always been a solution, I was looking at something that could be even simpler than that. We had always had the habit of using groovy scripts to make updates to the database. But I was wondering whether there would be some simpler solution via which I would be able to make the update just by using SQL queries at the prompt rather than spending time in writing groovy scripts, even if they be simple ones.

I explored a bit and came with the following solution. This was pretty simple and I was able to make the update within minutes. So, I thought I would share this with you. Whil some of you are awre of it, I sensed that there are a bunch of those, like me, who are indeed unaware of making large bunch of text content updates to the database.

To run SQL script files at the unix prompt, just do the following

• Type your queries, however big they be, in a text file. Let us say that we have a file sample.sql with an update query that is going to update the database column containing text data. Let us say that this text data contains HTML tags in it. So, typing this query as such at the psql prompt is going to be challenging. Copy pasting the query is again not possible because that is going to screw up the content that gets updated in the database. Save this sample file in your local machine.
• Create an ssh session to the remote server. Move this sample.sql file to the home directory in the remote server.
• Goto the remote server UNIX prompt and cd to your home directory to where you have the sample.sql file. Use psql command to get into PSQL prompt. You might be using the following command to get into the PSQL prompt

• [user@server folder]psql -h <host name>

• You might be asked for the password here. Type the correct password and you would be directed to the psql prompt from here.
• Once you get there, Type the following command to run the SQL script file sample.sql

• [user@server folder][databasename=>]\i <file name>

• And in this case it would be

• [user@server folder][databasename=>]\i sample.sql

• You would now get the confirmation stating

• UPDATE 1

• And that's it! You are done. You have made a large content update to the database in a minute!