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

  • texpic

    SSCertifiable

    Points: 5882

    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''

    WITH (FIELDTERMINATOR = ''","'', ROWTERMINATOR = '''')'

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

    (MyVarcharField)

    SELECT

    MyTableOneField

    FROM TableOne

  • Jeff Moden

    SSC Guru

    Points: 996046

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • ggavin 64175

    Newbie

    Points: 1

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

    ALTER PROCEDURE [dbo].[MoveFiles]

    (

    @fileName NVARCHAR(1000),

    @SourceDirectory NVARCHAR(1000),

    @DestinationDirectory      NVARCHAR(1000)

    )

    AS

    BEGIN

     

     

    SET NOCOUNT ON

     

    DECLARE

     

    @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

     

     

     

     

    END

     

     

    GO

     

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

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