Rename a text file, move it, using a stored procedure

  • I'm not sure I understand your question. Is all that data in a single field? If yes and all you want to do is import it and add the date/time this will work.

    --create a table called TableOne, single varchar(500) field

    -- bulk insert file

    DECLARE @BulkInsert as varchar(500)

    SET @BulkInsert =

    'BULK INSERT TableOne

    FROM ''C:MyTextFile''


    -- to get date/time think you need a second table

    -- create TableTwo, varchar(500) field and datetime field

    -- set default on datetime field to GETDATE()

    INSERT INTO TableTwo




    FROM TableOne

  • MrBaseball34 (8/26/2008)

    Kind of wondering how you would catch any errors when moving the file.

    I make a copy to a tempfilename passed into the procedure and then if there is an error when

    trying to move the file to it's permanent place, I'd like to copy the file to another place.

    No need if you use the "Move" command... if it fails, original file is preserved.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Made a proc from it, I'll certainly use it

    ALTER PROCEDURE [dbo].[MoveFiles]


    @fileName NVARCHAR(1000),

    @SourceDirectory NVARCHAR(1000),

    @DestinationDirectory      NVARCHAR(1000)










    @TodayDate VARCHAR(40),


    @TodayHour VARCHAR(40),


    @TodayMinu  VARCHAR(40),


    @NewFileName VARCHAR(100),


    @cmdstr  VARCHAR(128)



    SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)


    SELECT @TodayHour = DATEPART(hh,GETDATE())


    SELECT @TodayMinu = DATEPART(mi,GETDATE())


    SELECT @NewFileName = @fileName + '_' + @TodayDate + '-' + Right('0' + @TodayHour,2) +  Right('0' + @TodayMinu,2) + '.txt'


    PRINT @NewFileName


    SELECT @cmdstr='MOVE /Y  ' + @SourceDirectory + '\' + @fileName + ' ' +  @DestinationDirectory + '\' + @Newfilename


    PRINT @cmdstr


    CREATE TABLE #Moveout (moutput VARCHAR(1000))


    INSERT INTO #Moveout EXEC master..xp_cmdshell @cmdstr


    SELECT * FROM #Moveout


    DROP TABLE #Moveout










Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply