Text file into a Varchar column

Question

Text file into a Varchar column

guarddata

SSCertifiable

Points: 6583
More actions
October 15, 2003 at 5:01 pm

#81312

We have some template stored procedure stored in text files. Is there an easy way to stuff the file into a column in a database table? (VARCHAR(7500))
I can write code to do it, but wondered if there is an easy way directly from Query Analyzer, or EM.
Thanks
Guarddata-

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Well, I use this to get the contents of a file into a temp table. I'm sure you can figure out how to cursor through the rows and build your output string... good luck!



CREATE TABLE #table (line VARCHAR(8000))

-- Assume myfile.tmp file on C: drive root.

INSERT INTO #table 

EXEC xp_cmdshell 'TYPE c:\myfile.tmp'

SELECT line FROM #table

DROP TABLE #table

gljjr SSChampion Points: 10080 More actions · Answer 2

I'd probably be even more lazy(?). Since I use Visual Studio for all my dev work I would simply create a project with all the files. Then have Visual Studio create a command file for them (In a database project right clicking on a folder will give you this option). Run the command file against a database to load the sps and then grab the contents from syscomments. 🙂

This way you would also know that the SP's compile correctly before grabbing the data.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

guarddata SSCertifiable Points: 6583 More actions · Answer 3

Thanks Jay - sometimes I forget about cmdshell options.

Gary - Thanks to you also. Unfortunately, in this case the procedure is a template that will not compile by itself.

Guarddata-