How to modify bulk insert to use a wildcard

Posted on 2011-10-25
Last Modified: 2012-05-12
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
Question by:kat50
    LVL 21

    Expert Comment

    You'll have to use dynamic SQL.  Something like this.


    SET @DateTime = '1022011_195810'

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


    Author Comment

    I would like to not have to declare the date and time of the file.  Can I replace the + @DateTime + with '%'?
    LVL 21

    Accepted Solution

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now