June 22, 2009 at 9:41 am
I have a table containing filenames.
I call a function which uses various info including the number of rows currently in the table to generate the next filename.
The steps are as follows:
Foreach record to add to table
{
Generate new filename using function
Add record to table
}
Obviously I could use a cursor for this but I would really like to avoid them if possible.
The problem is that if I use the function in an INSERT SELECT statement it uses the same count(*) for all new rows it generates.
As a simple example, I am adding 2 new rows and the table is currently empty
INSERT INTO #Table1(Filename)
SELECT dbo.udf_GenerateNextFilename(Filename)
FROM #ListOfFiles
I'd end up with the following records in the table:
Filename_0
Filename_0
when what I should have is
Filename_0
Filename_1
Is there a way of doing this using a set-based approach?
Many thanks
June 22, 2009 at 10:15 am
How about using ROW_NUMBER and adding it to COUNT(*), it isn't terribly pretty but:
SELECT 'Filename_' + CAST((SELECT COUNT(*) FROM #ListOfFiles) + ROW_NUMBER() OVER (ORDER BY filename) AS VARCHAR(MAX)) FROM #ListOfFiles
Ed
June 22, 2009 at 10:36 am
How about posting the code for dbo.udf_GenerateNextFilename?
June 23, 2009 at 3:27 am
Many thanks for your replies.
Here is some sample data to help visualise the problem:
First create and populate the tables:
CREATE TABLE FileTable(
ChainID INT NOT NULL,
Filename VARCHAR(256) NOT NULL
)
INSERT INTO FileTable(ChainID, Filename)
SELECT 1, 'File-1_1'
UNION
SELECT 1, 'File-1_2'
UNION
SELECT 1, 'File-1_3'
UNION
SELECT 2, 'File-2_1'
UNION
SELECT 3, 'File-3_1'
UNION
SELECT 3, 'File-3_2'
CREATE TABLE ListOfFiles(
Filename VARCHAR(256) NOT NULL
)
INSERT INTO ListOfFiles(Filename)
SELECT 'NewFileA'
UNION
SELECT 'NewFileB'
UNION
SELECT 'NewFileC'
Next create udf_GenerateFilename()
CREATE FUNCTION [dbo].[udf_GenerateFilename]
(
@ChainID INT,
@FileName VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @NewFileName VARCHAR(70)
DECLARE @FileCount INT
SELECT @FileCount = 1 + Count(*) FROM FileTable WHERE ChainID = @ChainID
SELECT @NewFileName = @Filename + '-' + CAST(@ChainID AS VARCHAR(10)) + '_' + CAST(@FileCount AS VARCHAR(10)) + '.CSV'
RETURN @NewFileName
END
Now I would like to be able to generate the next 3 filenames for ChainID = 2 using the records in table ListOfFiles. The new entries in FileTable should be as follows:
2, NewFileA-2_2.CSV
2, NewFileB-2_3.CSV
2, NewFileC-2_4.CSV
If I use the following code example
INSERT INTO FileTable(ChainID, Filename)
SELECT 2, dbo.udf_GenerateFileName(2, Filename)
FROM ListOfFiles
then the filenames are incorrectly added to FileTable as follows
2, NewFileA-2_2.CSV
2, NewFileB-2_2.CSV
2, NewFileC-2_2.CSV
Is there a way to do this without iterating through the records and running the function on every line separately?
Thanks again,
Paul.
June 23, 2009 at 5:45 am
Do you have to use a function? If not you can use the query I posted above to generate the file.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply