October 11, 2015 at 4:51 pm
I am trying to break apart a list of filenames that was inserted into a database. It only breaks out the first one then moves onto the next record. If I do them individually then seem to work but not the whole table when queried. I need to break out each file into a temp table then insert them into a documents field in a database.
my filenames look like so and can have from 1 file name to 10 file names in the string.
test.pdf,test2.pdf,test4.tif,test5.pdf
somedoc.tif,test.docx,test4.pdf
This is my current method, I needed to create a cursor around it to go through all the records, split out the filenames and insert into a temp table. But if there is a better way ill do it. The problem with this is only the first file is getting inserted into the temp table and nothing else even if the filename has 4 files in it.
Create table #tempFiles (OldStrId int, OldPercent int, strfilename varchar(max), RequestId int, OblId int)
declare @OldStr int, @OldPer int, @FileName varchar(max), @intcount int;
Declare filenames CURSOR FOR Select intSTRBonusID, intPercentID, strFileName from tblSTR where strFileName > ''
UNION ALL
Select intSTRBonusID, intPercentID, strFileName from tblSTRHist where intPercentID in (61,62) and strFileName > ''
order by intSTRBonusID
OPEN filenames
FETCH NEXT FROM filenames INTO @OldStr, @OldPer, @FileName
while @@FETCH_STATUS = 0
BEGIN
DECLARE @FoundIndex INT
DECLARE @ReturnValue VARCHAR(MAX)
SET @FoundIndex = CHARINDEX(',', @FileName)
WHILE (@FoundIndex <> 0)
BEGIN
SET @ReturnValue = SUBSTRING(@FileName, 0, @FoundIndex)
SET @FileName = SUBSTRING(@FileName, @FoundIndex + 1, len(@FileName) - @FoundIndex)
SET @FoundIndex = CHARINDEX(',', @FileName)
END
IF NOT Exists (Select OldStrId from #tempFiles where strfilename = @FileName)
BEGIN
INSERT #tempFiles (OldStrId, OldPercent, strfilename) VALUES (@OldStr, @OldPer, @FileName)
END
FETCH NEXT FROM filenames INTO @OldStr, @OldPer, @FileName
END
Close filenames
Deallocate filenames
select * from #tempFiles
October 11, 2015 at 8:13 pm
Yes there is a better way. The string you are using for the filenames looks like a separated list of values to me. There are a number of table valued functions that you can use to take this list and turn it into a table of values. Try searching the script library here at SQL Server Central - you should be able to find one.
October 11, 2015 at 8:36 pm
It looks to me like you need an efficient way of splitting your comma-delimited lists by the comma. Jeff Moden has an excellent article on just such a splitter. Check out the article at http://www.sqlservercentral.com/articles/Tally+Table/72993/ for the DelimitedSplit8K function. I think it's exactly what you're looking for.
Here's an example of how simple it is to split out a column of delimited strings:
SELECT s.ItemNumber, s.Item
FROM dbo.tblSTR d
CROSS APPLY dbo.DelimitedSplit8K(t.strFileName, ',') s
If you need the filenames to be split into different columns in the same row, there's a technique called a crosstab query that may be able to help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply