ms sql 2015

Bryon Huffman
Bryon Huffman used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoMSSQL Senior 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ãoMSSQL Senior 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ãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
2 different solutions provided.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial