SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamic Filename
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jimbojames30
Starting Member

United Kingdom
2 Posts

Posted - 06/02/2014 :  05:38:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 06/02/2014 :  10:26:14  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 06/02/2014 :  16:49:04  Show Profile  Reply with Quote
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

Go to Top of Page

Jimbojames30
Starting Member

United Kingdom
2 Posts

Posted - 06/07/2014 :  12:38:53  Show Profile  Reply with Quote
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,
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000