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.
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:
-- This Works
BULK INSERT MyDatabase..MyView FROM 'MyPath\MyFile.txt'
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:
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 53046
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.
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.
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.