Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: Bulk Insert statement

    Hi,

    I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.

    Suppose there is a parameter @loc,so i want to use this parameter as

    Bulk insert TableName from @loc with(fieldterminator=',')
    plz help me out thx

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2006
    Posts
    4

    Bulk Insert

    Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Did you even try dynamic sql as suggested to you?
    Code:
    declare @fn varchar(255)
    set @fn='\\mypc\tmp\tmp1.txt'
    exec ('bulk insert mytable from '''+@fn+''' with(fieldterminator='','')')

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Post

    Quote Originally Posted by rahul441
    Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
    u haven't replied to Satya's solution.Then how we know that u got the answer or not?
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    May 2006
    Posts
    4

    Bulk Insert

    Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.

  7. #7
    Join Date
    May 2006
    Posts
    4
    also i did try this code-:
    @loc nvarchar (50)
    Bulk Insert Tablename From '''@loc'' with(fieldterminator='','')
    but i get an error while saving this stored procedure which says
    "Cant find @loc"

  8. #8
    Join Date
    Jun 2003
    Posts
    269
    post ur stored procedure
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by rahul441
    Even here u are giving the static location
    No, I am using a variable. Here are some code for you to play with
    Note: UNC path used for the load as the server is not running on my local PC.
    Code:
    C:\tmp>echo aaa,bbb >tmp.txt
    
    C:\tmp>more tmp.txt
    aaa,bbb
    
    C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
    Password:
    1> use tempdb
    2> go
    1> create proc pdreyer_load
    2> @filename varchar(255)
    3> as
    4> exec ('bulk insert #t1 from '''+@filename+''' with(fieldterminator='','')')
    5> go
    1> create table #t1 (f1 varchar(10),f2 varchar(10))
    2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
    3> select * from #t1
    4> go
     f1         f2
     ---------- ----------
     aaa        bbb
    
    (1 row affected)
    1> drop table #t1
    2> drop procedure pdreyer_load
    3> go
    1> exit
    
    C:\tmp>del tmp.txt

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Is the source file changed all the time?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •