Read from text file into sql
Posted on 2014-01-22
I need to look up text files from a query then do a bulk insert into table so I can retrieve the text file contents from a query. Need help on getting somthing to work.
based on this query I can pull the file name from the note_id field
SELECT note_id ,enc_id, person_id, table_name FROM patient_notes WHERE person_id = '123' AND table_name in ('assess_comm_') AND (practice_id = '0001' AND enc_id ='0') OR enc_id ='xyz' ORDER BY table_name, field_name, enc_id DESC
pull the file contents from each of the records
-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c:\2CB3FB21-05CF-4F11-B2A1-4A19355EE1C0.TXT'
--Need to add the enc_id to the #textfile table
--need to loop through 4 records then move on to next person_id '1234" and continue --appending to #textfile
If you can help me create a working script I would appreciate it.
Thanks in advance