SQLTeam.com | Weblogs | Forums

Read text file into query variable


#1

The SQL I've written returns the records for the job numbers I have in the WHERE clause. In the example of the WHERE clause, below, I have three current jobs but from time to time there will be more or fewer. I want to have the active job numbers listed in a text file that can be revised as necessary and have the SQL read the file and put the job numbers in a variable which would be referenced in the WHERE clause.

WHERE (dbo.RELDTL.JOB_NO IN ('640', '700', '725'))

Example Job Numbers text file (no header, just job number)
640
700
725

Two months from now it might be
700
725
800
805

I would probably write the text file with single quotes around the job numbers as management tends to change format.
'700'
'725'
'18-00'
'18-05'

This way I need only revise the text file, which anyone in the department cold do, rather than revise the SQL which I wouldn't want anyone to do.

I found some code that might do it but I "do not have permission to use the bulk load statement."

DECLARE @Job_numbers VARCHAR(10)

SELECT @Job_numbers=BulkColumn
FROM OPENROWSET(BULK'c:\temp\job_numbers_file.txt', SINGLE_BLOB) x:

The text file would actually reside on the "K" drive in a different folder and be very small and at most have ten or fifteen job numbers in it, generally around six. The job numbers could be listed vertically or horizontally. I would appreciate any assistance, thank you.
Ed


#2

so a question, what is this process part of? why are you doing what you are doing? what is the end goal?


#3

--I found some code that might do it but I "do not have permission to use the bulk load statement."
--Have the DBA grant you 'bulkadmin' server-level permission, like this:
EXEC sys.sp_addsrvrolemember '<your_sql_login_or_group>', 'bulkadmin'

Here's the code. I've used sample data in-line, but of course you can comment that out and use the OPENROWSET() when available.

DECLARE @Job_numbers VARCHAR(8000)

--SELECT @Job_numbers = BulkColumn
--FROM OPENROWSET(BULK 'c:\temp\job_numbers_file.txt', SINGLE_CLOB) x(BulkColumn);

SET @Job_numbers = '700,
725 800     
805'

--Replace tab/cr/lf/, with spaces, then compress multiple spaces into a single space.
SET @Job_numbers = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Job_numbers, 
    CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), ',', ''),
    SPACE(33), SPACE(1)), SPACE(17), SPACE(1)), SPACE(9), SPACE(1)), 
    SPACE(5), SPACE(1)), SPACE(3), SPACE(1)), SPACE(2), SPACE(1)), 
    SPACE(2), SPACE(1))

--Use "standard" splitter (Google for the code if you don't already have it)
--to get the final values.
SELECT CASE WHEN LEFT(Item, 1) <> '''' THEN '''' ELSE '' END +
    Item +
    CASE WHEN RIGHT(Item, 1) <> '''' THEN '''' ELSE '' END AS Job_Numbers_List
FROM dbo.DelimitedSplit8K(@Job_numbers, SPACE(1))

#4

I retrieve records of members in the company legacy database and use them in a MS Access database via a csv file that the afore mentioned SQL ultimately creates. I can get the legacy database to create the csv file from a menu item in the program software but that csv file returns far more data than I need (columns and extraneous rows) so I have to use an Excel spreadsheet and VBA to massage the data to what I need then have the Access database fetch the data from the Excel spreadsheet. We use oooold versions of Access and Excel and I have problems there, also I'm the only one that does it and if I don't it doesn't get done (each morning).

I'm going to have the SQL, of which the afore mentioned WHERE clause is a part, run in a SQL Server Agent job and create the csv file which the Access database can then take in directly. All this is going to be moved to another platform in a year or two. I'm just trying to get the job numbers in the WHERE clause without having to change the SQL each time I want to add a job(s) or take out a job(s).


#5

They are real stingy with permissions, I'm lucky I get to do what little I can do. I believe they would rather I revised the SQL each time I wanted to add or delete job(s) then save it back for the SQL Server Agent job.


#6

'bulkadmin' is not at all a major permission, it literally only allows you to do bulk inserts. Can't imagine why they would object to that, since bulk inserts are far more efficient for when lots are rows being inserted.


#7

@EJB,
What version of SQL Server are you using. SQL Server 2016 added External Tables.


#8

I have SQL Server Management Studio 2008 on my computer and we have SQL Server 2008 R2 (SP3) (10.50.6220.0 (X64)) on the network server. I've talked with the IT guy and he might/probably give me the permission to use bulk load statements but I would still like a way to do as I described in my original post, read a text file and put the comma separated list in the variable that I can use in my WHERE statement. I'm not loading it in the database just developing the list of jobs I want included in my query.

After running the following SQL (or something like it) earlier in the main SQL file;

DECLARE @Job_numbers VARCHAR(10)

SELECT @Job_numbers=BulkColumn
FROM OPENROWSET(BULK'c:\temp\job_numbers_file.txt', SINGLE_BLOB) x:

I would have the variable in the WHERE clause like this;

WHERE (dbo.RELDTL.JOB_NO IN (@job_numbers))

if possible.
Thank you, Ed


#9

To make that work you need to use dynamic SQL and build your SQL statement by concatenating your variable.


#10

Could I impose on you to give me some examples/samples to help me get started.
Thank you, Ed


#11
DECLARE 
      @Job_numbers VARCHAR(1000) = (SELECT BulkColumn
FROM OPENROWSET(BULK'c:\temp\job_numbers_file.txt', SINGLE_BLOB) x);
DECLARE
      @sql nvarchar(1000) = 'SELECT * FROM dbo.RELDTL WHERE JOB_NO IN (' + @job_numbers + ')';
EXEC (@sql);

#12

Hopefully the IT guy will get me the "bulk" permission(s) soon and I can try this. The SQL that I'm running gets a total of 14 columns from 4 tables and has another two tables associated to bring forth the data I need (six tables joined). I'm assuming that SQL (for the 14 columns of data) goes in after the "nvarchar(1000) =" part. I'm also assuming I'll have to change the "nvarchar(1000)" to "max" or "1000000" or something like that. This SQL will return 70,000± records with 14 columns. Thank you, Ed