CODEWITHSUNDEEP
sql-servert-sqlbulkinsert
Duane Mitchell
Duane Mitchell

Reputation: 61

SQL Server: Bulk Insert data from file nightly

I am trying to build a scheduled procedure to nightly grab a file that is ftp'd to a server and do a BULK INSERT with the contents.

The problem I face is that the filename changes each day with a date/timestamp. I need to build this SQL command:

BULK INSERT TableName
FROM '\\server\directory_name1\directory_name2\date_time_filename.csv'

The "filename.csv" never changes, the directory the file goes into never changes, and this is supposed to happen on a schedule.

It doesn't always successfully do that. So even if I could somehow CONCAT the date, time, and filename into the FROM statement the file might not exist because it failed to get there at the usual time.

I also don't think I can CONCAT into a @variable and use it in the FROM as in: FROM @variable. Anyone know if it's possible? . Next I suppose I will look at accessing the directory and browse it for files but that seems a little bit kludgy

Upvotes: 0

Views: 373

Answers (2)

Duane Mitchell
Duane Mitchell

Reputation: 61

Thank you so much. That solved the issue. I added to it by adding a CONCAT of the various config settings. Also, the server script runs at 23:00 PM and I don't work with that file until the next day so the date is off by one day. So I had to do some DATEADD work. I did 2 CONCATS to make it easier to work with. Too many quotes to double quote, etc. It gets befuddling. My finished code is this:

DECLARE @sqlCmd NVARCHAR(400);
DECLARE @now DATETIME2 = GETDATE();

SET @sqlCmd = CONCAT(
                'BULK INSERT dbo.MyTable FROM ''', '\\server\directory1\directory2\'
                , FORMAT((SELECT DATEADD(DAY, -1, @now)), 'yyyy-MM-dd'), '_2300_filename.csv''');

SET @sqlCmd = CONCAT(@sqlCmd,
' WITH ( FIRSTROW = 2,
CODEPAGE=65001,
DATAFILETYPE = ''char'', 
BATCHSIZE = 50, 
FIELDTERMINATOR = '';'', 
ROWTERMINATOR = ''0x0A'' 
);'
);

Upvotes: 0

Thailo
Thailo

Reputation: 1404

I would do something like this:

DECLARE @sqlCmd NVARCHAR(400);

SET @sqlCmd = CONCAT(
                'BULK INSERT dbo.TableName FROM ''', '\server\directory_name1\directory_name2\'
                , FORMAT(GETDATE(), 'yyyyMMdd_HHmmss'), '_filename.csv''');

-- PRINT @sqlCmd;
-- EXEC sys.sp_executesql @sqlCmd;

Upvotes: 1

Related Questions