Dynamic file name for Bulk Insert

Fly Girl · #1 (**permalink**) 10-20-05, 15:47

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!

Brett Kaiser · #2 (**permalink**) 10-20-05, 16:31

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

Fly Girl · #3 (**permalink**) 10-20-05, 16:52

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.

Brett Kaiser · #4 (**permalink**) 10-21-05, 08:59

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....

Fly Girl · #5 (**permalink**) 10-24-05, 13:18

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.

Fly Girl · #6 (**permalink**) 10-24-05, 15:45

Got the archive piece working by giving the world perms on my test setup. It looks good, thanks a bunch.

Brett Kaiser · #7 (**permalink**) 10-25-05, 09:08

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?

Fly Girl · #8 (**permalink**) 10-25-05, 12:41

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!

Brett Kaiser · #9 (**permalink**) 10-25-05, 13:38

Just curious...did you combine the dos sproc together with bcp?

Fly Girl · #10 (**permalink**) 10-25-05, 14:17

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech