17

Could someone tell me how to read a file's contents into an MS SQL variable using T-SQL?

Babu James
  • 2,740
  • 4
  • 33
  • 50
  • Explain more on what you are trying to do exactly. What sort of content are you trying to read and into what type of variable? Do you mean importing the contents of a flat file into a table using tsql? – vmvadivel Jul 18 '12 at 10:59
  • I want to read a *.sql file and run the script using EXEC command. – Babu James Jul 18 '12 at 11:34
  • You might also want to look at `sqlcmd` mode and [the `:r` switch](http://blogs.msdn.com/b/patrickgallucci/archive/2007/09/03/sqlcmd-and-the-power-of-the-little-r.aspx) depending on why you need this. – Martin Smith Jul 18 '12 at 11:46
  • This link might help you on this: http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ – AnandPhadke Jul 18 '12 at 11:01
  • That needs me to create an SP which I can't do – Babu James Jul 18 '12 at 11:35

2 Answers2

37
DECLARE @FileContents  VARCHAR(MAX)

SELECT @FileContents=BulkColumn
FROM   OPENROWSET(BULK'PathToYourFile.sql',SINGLE_BLOB) x; -- BINARY
--FROM OPENROWSET(BULK'PathToYourFile.sql',SINGLE_CLOB) x; -- CHAR

The SQL Server service account needs to have permissions to read the file obviously.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I tried your method, it definitely works! A big thank you. However, I do not seem to read the entire file. It's either not fully loaded into the variable or it's loaded in a binary form. How do I load the complete text in ASCII form? – Babu James Jul 18 '12 at 14:44
  • @BabuJames - Did occur to me that the variable might need to be `nvarchar` so maybe try that. Also how are you establishing that it is not fully loaded? SSMS will truncate the output of long variables. See [how to get the full resultset from SSMS](http://stackoverflow.com/questions/8930034/how-to-get-the-full-resultset-from-ssms/8930141#8930141) for a work around. – Martin Smith Jul 18 '12 at 14:50
  • All your suggestion did help me figuring out more about the issue even though it still remain unresolved. The scenario is: I have about 10 backup files of the same database. Periodically I need to sync them with the latest model changes. So, right now I have to restore each file, run the script, and backup. Therefore, I thought of some kind of automation for this and came up with the idea of one script that will restore each one by one and do the operations. Everything else is fine, but the execution of locally saved .sql file on them is problematic. – Babu James Jul 19 '12 at 09:19
  • contd. I have no way to run the script after restoring my backup. Following are the problems occurred: 1. When I use the xp_cmdshell and sqlcmdline to run the file, I get no permission error. 2. When I try to read the file into memory using OPENROWSET, I can't read the entire file. Could you please shed more light in this regard. – Babu James Jul 19 '12 at 09:19
2

Make use of SQLCMD to execute the .sql (either from command prompt or within SSMS). If you want to use it within SSMS then first turn the SQLCMD mode (Query >> SQLCMD Mode)

Check out http://msdn.microsoft.com/en-us/library/ms174187.aspx

:r yourFilename

something like:

:r d:\scripts\sample.sql
vmvadivel
  • 1,041
  • 5
  • 7