Using variable to define BULK INSERT file path

Posted on 2007-10-20
Last Modified: 2008-07-02

this is probably a noddy question.. how do i provide the BULK INSERT statement with the data_file info as a variable, ie replace '..mypath..myfile.txt' with eg @Filepath (if i do this i get error  Incorrect syntax near '@Fullpath'.)

BULK INSERT dev.dbo.tmpFileHeader FROM '..mypath..myfile.txt' with (Lastrow=1)

Question by:xenium
    LVL 142

    Accepted Solution

    you will need dynamic sql:

    declare @sql varchar(8000)
    set @sql = 'BULK INSERT dev.dbo.tmpFileHeader FROM ''' + @FilePath + ''' with (Lastrow=1)'
    exec (@sql)

    Author Comment

    Great, thanks!

    Author Comment

    For my reference, this in an sp:

    CREATE PROCEDURE sp_GetFileheader @Filepath varchar(500) AS

    drop table tmpFileHeader
    create table tmpFileHeader (HeaderText char(100))

    declare @sql varchar(8000)
    set @sql = 'BULK INSERT dev.dbo.tmpFileHeader FROM ''' + @FilePath + ''' with (Lastrow=1)'
    exec (@sql)
    select * from tmpFileHeader


    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

    Join & Write a Comment

    Suggested Solutions

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now