We help IT Professionals succeed at work.

ms sql 2015

I am bulk loading a file nightly and want to rename the file as part of the store procedure.
In stored procedure do bulk load and then rename the file name so that you can tell the file has been bulk loaded.
Comment
Watch Question

ITSysTechSenior Systems Administrator

Commented:
Are you using MSSQL 2016 the heading says 2015?  thanks

Author

Commented:
Sorry ms sql 2008
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please find the complete solution below -

CREATE TABLE BulkExamples
(
	ID INT,
	Name VARCHAR(40)
)
GO

BULK
INSERT BulkExamples
FROM 'c:\pawan\tblName_March_2015.txt'
WITH
(
	FIELDTERMINATOR = '	',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM BulkExamples

Open in new window


OUTPUT

/*------------------------
SELECT * FROM BulkExamples
------------------------*/
ID          Name
----------- ----------------------------------------
1           Pawan
2           Sharlee
3           Jason
1           Pawan
2           Sharlee
3           Jason

(6 row(s) affected)

Open in new window


For rename the file you can download the code from http://www.itprotoday.com/t-sql/rename-files-or-directories-fly-t-sql-scripts

Execution -
exec sp_ReplaceFileOrDirNames @pathToObject= 'c:\pawan',
                              @oldName='tblName_March_2015.txt',
                              @newName='tblName_March_2015_DONE_BULK.Txt'

Open in new window


For renaming if the above solution does not work we can also use xp_cmdshell.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
You shouldn't use SQL Server to perform operation system tasks as renaming a file.
I will recommend you a solution that runs from the Windows command prompt. You can use the Windows Task Scheduler to schedule the following script to run it daily:
bcp databaseName.dbo.FileName IN C:\importFiles\FileName.bcp -c -T
ren C:\importFiles\FileName.bcp C:\importFiles\NewFileName.bcp

Open in new window

BCP is a utility that performs the Bulk Insert and ren is the rename command.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Bryon, is your issue solved?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi Bryon,
Did my comment worked for you ?

Thanks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
2 different solutions provided.