Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Dynamic file name for Bulk Insert

    SQL Server 2K

    OK, I'm probably being a bone-head here and am clearly in over my head but how do you (or can you?) set up a Bulk Insert to take a dynamic path/file name?

    What I want to do is pass in the path and file name from an external process to a stored procedure that bulk inserts the content of the file and then does some other routines on it. I haven't had any luck getting Bulk Insert to run if the path/file name is not hard-coded in the sproc as a string.

    The point is to have a master routine that can exercise the process for several different customers and use meta data in a table to inform what file to bulk insert.

    Any suggestions?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just put any text file in that location to run this test...

    Oh, and learn to love bcp....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    
    DECLARE @fn varchar(8000)
    SELECT @fn = 'd:\test.txt' 
    
    /* Will Fail
    BULK INSERT Northwind.dbo.myTable99
       FROM @fn
    */
    
    DECLARE @cmd varchar(8000)
    SELECT @cmd = 'bcp Northwind.dbo.myTable99 IN ' + @fn + ' -c -T -S' + @@SERVERNAME
    EXEC master..xp_cmdshell @cmd
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Thanks Brett!

    If I have a chance I'll give it a try. We're supposed to run 30 files through this process this weekend and it would be awfully nice to have a little automation on our side.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just a little automation?

    Some flygirl

    http://weblogs.sqlteam.com/brettk/ar...6/28/6895.aspx

    Then just use a cursor on the "dos dir" table and use my bcp I gave you...I think I did an archiving thing somewhere....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Tough love

    Brett, really, I'm trying to love bcp. However, it's a challenging relationship due to a terrible lack of communication. He just won't open up and talk to me. All this stonewalling is making me insecure and I may have to return to previous relationships. But I'd really like to work it out...

    Here's what I've got:

    Code:
    -- This Works
    BULK INSERT MyDatabase..MyView FROM 'MyPath\MyFile.txt'
    		WITH (
    			DATAFILETYPE = 'widechar',
       			FIELDTERMINATOR = '|',
       			ROWTERMINATOR = '\n'
    			)
    
    
    -- This doesn't work
    DECLARE	@cmd varchar (8000)
    DECLARE @FName	varchar (100)
    DECLARE @FPath	varchar	(500)
    
    SELECT 	@FName = 'MyFile.txt'
    SELECT 	@FPath = 'MyPath\'
    
    SELECT	@cmd = 'bcp MyDatabase..MyView IN "' + @FPath + @FName + '" -w -T -S' +@@ServerName
    
    EXEC	master..xp_cmdshell @cmd
    The bcp chunk gives me:

    NULL
    Starting copy...
    NULL
    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 53046
    NULL

    And, indeed, it copied no rows. I've tried passing in a -t\| because the files are pipe delimited but then it won't even run. The row terminator is the default and SQL Server tells me that the file is widechar.

    Did I miss something?

    Also, I do have the archive piece. However, when I try to run it I get annoying access errors. It may work if I run it off of Prod with the correct login but I haven't gotten that far with all of this yet.
    Last edited by Fly Girl; 10-24-05 at 14:26.

  6. #6
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Got the archive piece working by giving the world perms on my test setup. It looks good, thanks a bunch.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you using -w btw?

    What's the file look like? Looks like your file is pipe delimited, at least based on your BULK INSERT statement.

    You need to specify that using -t| and probaly you need to use -c as well


    It actually might be -t"|"....I'll have to play around with it....

    BUT! BULK INSERT is ok as well....as long as you're not using DTS.

    Using bcp or BULK INSERT you can automate stuff easier usiong dynamic SQL. DTS would be such a pain

    Did you get all your files loaded?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Yahooooooo!

    THANK YOU, THANK YOU! THANK YOU! And where do I send the margaritas?

    OK, all it wanted was the double quotes around the pipe. DOH! Guess that was why it wouldn't run when I included the -t| before. It would be so lovely if it could at least give me a hint at where the problem is. I studied the info from the books online extensively and they don't mention or give examples of what to do if it is not tab delimited other than that you could follow -t with another parameter to override the default. I'm afraid I'm a bit of a dolt when it comes to running code at the DOS prompt. I stricktly avoided coding at all when that was the main method. However the more I write code, the more I end up there.

    Code:
    SELECT	@cmd = 'bcp MyDB..MyView IN "' + @FPath + @FName + '" -w -t"|" -T -S' + @@ServerName
    With that change I successfully copied 66123 rows to my destination table via the view. This is perfect!

    FYI, I'm using -w because Query Analyzer politely told me that I should be using widechar the first time I ran the Bulk Insert. Communication is a wonderful thing.

    This will be great to have this working. It will mean taking all of this mess to a much better level of automation. Thank you so much for the hand-holding.

    Whohooo! This made my day!
    Last edited by Fly Girl; 10-25-05 at 13:45.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious...did you combine the dos sproc together with bcp?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    I was just celebrating running a single file through bcp. However I will look at the other script because we have processes here that would happily use that.

    BTW the 30 files got combined into one--saving only the last row sent for any SSN. Then I ran system updates only once to catch up a database to the last 30 weeks of employee rosters. It was one of those 'write code to update a gazillion tables' on a database I wasn't familiar with. Got the job on Tuesday afternoon and it absolutely had to be done by Friday COB. Thursday I found out that updates needed to be processed for 30 weeks of files. The idea of running system updates 30 times was not a good one especailly on totally raw code that could end up duping records 30 times or something painful like that.

    I can usually find ways to get my work done on time but I really appreciate the chance to improve HOW I'm getting it done. You've been a big help.

    Thanks!

Posting Permissions

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