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

• texpic

  SSCertifiable

  Points: 5885

  More actions

  August 26, 2008 at 5:57 pm

  #863454

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

  More actions

  August 26, 2008 at 6:09 pm

  #863457

  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)
  

• ggavin 64175

  Newbie

  Points: 1

  More actions

  March 26, 2020 at 3:14 pm

  #3737209

  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