Menu
Could someone tell me how to read a file's contents into an MS SQL variable using T-SQL?
664
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
Syntax: SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name; This query will read the content of the text file and return it as a single column in a table named Bulkcolumn. The correlation name is mandatory to specify.
SQL Declare variable string To declare a string variable, use the DECLARE keyword, then type the @variable_name and variable type: char, varchar. To assign a value to a variable, use the keyword SET. The CHAR is a data type with fixed length and loads empty spaces.
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.
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
