Dynamic Filename - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic Filename

Author

Topic

Jimbojames30
Starting Member

8 Posts

Posted - 2014-06-02 : 05:38:17

Hi All,

New to SQL server so looking for som advise, i want to use BULK LOAD and save it as a stored procedure, problem is that the file name is dynamic and it always contaians the date and time it was saved in the file name

I used to get arround this in MS access by doing the import via VBA (Code below) but not sure how i can achive this in SQL server, Any help would be greatly appreciated :-)

(Example file name AIN0026_SO_LEAPLIVE_20140530005004_20140529_130011.txt)
the vba would just look for AIN0026_SO_LEAPLIVE_

Dim LeapLiveFolderLoc As String
LeapLiveFolderLoc = DMax("[Path]", "TblsysDirectory", "[Process] = 'LeapLive Import'")

Dim AIN0026 As String
AIN0026 = Dir$(LeapLiveFolderLoc & "AIN0026_SO_LEAPLIVE_*.txt")

DoCmd.TransferText acImportDelim, "AIN0026_SO_LEAPLIVE", "Tbl_AIN0026_SO_LEAPLIVE", LeapLiveFolderLoc & AIN0026

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-02 : 10:26:14

pass the variables (date and time) to the stored proc then build the filename from those. then, pass the generated filename to the bulk load operation

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-02 : 16:49:04

If xp_cmdshell is available, you can use it to do a "dir" command, loading the results of the "dir" into a table, like so:


CREATE TABLE #files (
    filename varchar(255)
    )
DECLARE @cmd nvarchar(4000)

SET @cmd = 'dir /b "' + @LeapLiveFolderLoc + 'AIN0026_SO_LEAPLIVE_*.txt"'

INSERT INTO #files ( filename )
EXEC xp_cmdshell @cmd

SELECT * FROM #files

Jimbojames30
Starting Member

8 Posts

Posted - 2014-06-07 : 12:38:53

quote:
Originally posted by ScottPletcher

If xp_cmdshell is available, you can use it to do a "dir" command, loading the results of the "dir" into a table, like so:

CREATE TABLE #files (
filename varchar(255)
)
DECLARE @cmd nvarchar(4000)

SET @cmd = 'dir /b "' + @LeapLiveFolderLoc + 'AIN0026_SO_LEAPLIVE_*.txt"'

INSERT INTO #files ( filename )
EXEC xp_cmdshell @cmd

SELECT * FROM #files

Great, thanks for your response its greatly appreciated, and thank you got the syntax this will help me out greatly,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources