dynamic file location in T-SQL
-
Hello all,
I want to set the location of some files that I'm loading with bcp in a column. Is there a way to do something like this:
SELECT @IMPORT_FILE_PROP = (database.table.column_X) + '\file.TXT'
Column_X would contain information like \\servername\directory. If this is possible, what would the syntax look like? I hope I'm being clear about this.
Thanks...
-
It's not clear to me what you're asking.
If you are loading data from a file, but want that data to be slightly different in the table you're loading it to, then first load it and then update the table.
If it's not that, then maybe give an example...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
SELECT @IMPORT_FILE_PROP = column_X + '\file.TXT'
from database.Schema.table
Where <>
-
Let me try to be more clear...
The data won't change from the text file to the table. I just need the location of the text file to be referenced within a column. In other words, if I'm storing the text file in \\server\directory\files\text.txt, column_X needs to be the string value '\\server\directory\files\text.txt' so that the script knows to look there for the text files.
Clear as mud? ^_^
-
So you just want to set a variable with a value that's in a table? That simple? If so, here's an example...
--example data
declare @t table (id int, column_X varchar(100))
insert @t
select 1, '\\server\directory\files\text.txt'
--declare and set variable
declare @IMPORT_FILE_PROP varchar(100)
select @IMPORT_FILE_PROP = column_X from @t where id = 1
--show results
print @IMPORT_FILE_PROP
/*results
\\server\directory\files\text.txt
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Right. I see what you're saying. The thing is, the table containing the preset column value already exists in the database. Couldn't I do this without declaring a new table and just use the value from the already existing table?
Maybe something like this:
select @IMPORT_FILE_PROP = existing_column from existing_table
...then tack on the individual file names.
See, there is more than one text file that needs to be imported. I'm just wanting to tell SQL where to look for the files. So I guess my second question is how would I import multiple files within the same directory? All the files are going to be in the same directory, but have different names.
For instance,
'\\server\directory\files\' + file1.txt
'\\server\directory\files\' + file2.txt
'\\server\directory\files\' + file3.txt
What would the syntax look like for something like that?
Thanks for your help...
-
Use a cursor to get the filename from each row in the table, then import that file within the cursor loop.
Mark -
> Couldn't I do this without declaring a new table and just use the value from the already existing table?
Yes. Exactly that. My "example data" was just that - as an example. You can use MyTableName in place of @t and you don't need the "example data" section.
> Use a cursor to get the filename from each row in the table, then import that file within the cursor loop.
Yes. I was just working on an example of that.
--example data
declare @t table (column_X varchar(100))
insert @t
select '\\server\directory\files\text.txt'
union all select '\\server\directory\files\text2.txt'
union all select '\\server\directory\files\text3.txt'
--calculation (import)
DECLARE MyCursor CURSOR
READ_ONLY
FOR select column_X from @t
DECLARE @column_X varchar(100)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @column_X
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'Put your code here - e.g. BULK INSERT MyTable FROM ' + @column_X + ' WITH (FIELDTERMINATOR = ''","'')'
PRINT ''
END
FETCH NEXT FROM MyCursor INTO @column_X
END
CLOSE MyCursor
DEALLOCATE MyCursor
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I believe I got it guys. I was making it harder than it needed to be.
SELECT @IMPORT_FILE_PROP = INSTALL_DIRECTORY + '\file1.TXT' from county_master
...
SELECT @IMPORT_FILE_PROP = INSTALL_DIRECTORY + '\file2.TXT' from county_master
...
SQL Server found/loaded the files with no problem. I just reran this line for each of the four files.
Thanks again to everyone for your help...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply