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

    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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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