[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1466
  • Last Modified:

How to modify bulk insert to use a wildcard

How can I modify my bulk insert SQL to use a wildcard?  The first of the filename will always be the same, but appended with a date and time. Current file is: MyCompany_Name_1022011_195810.txt and I

BULK INSERT #tmp_Test_Feed
FROM ''W:\MyCompany_Name_1022011_195810.txt
WITH
(
ROWTERMINATOR = '\n'
 )
0
kat50
Asked:
kat50
  • 2
1 Solution
 
JestersGrindCommented:
You'll have to use dynamic SQL.  Something like this.

DECLARE @DateTime NVARCHAR(25), @SQL NVARCHAR(MAX)

SET @DateTime = '1022011_195810'

SET @SQL = 'BULK INSERT #tmp_Test_Feed
FROM ''W:\MyCompany_Name_' + @DateTime + '.txt''
WITH
(
ROWTERMINATOR = ''\n''
 )'
 
 EXECUTE sp_executesql @SQL
 
 Greg

0
 
kat50Author Commented:
I would like to not have to declare the date and time of the file.  Can I replace the + @DateTime + with '%'?
0
 
JestersGrindCommented:
I hard coded the date and time as an example.  You have to have the name of the file for it to work.  It cannot be a wild card.  

Take a look at the article below.  It provides a way to get the name of the file and then import it using BULK INSERT.

http://www.databasejournal.com/features/mssql/article.php/3325701/Import-multiple-Files-to-SQL-Server-using-T-SQL.htm

Greg

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now