Problem finding set-based approach to query

  • 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

  • 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

  • How about posting the code for dbo.udf_GenerateNextFilename?

  • 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,

    @FileNameVARCHAR(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.

  • 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