If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Dynamic file name for Bulk Insert

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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 13:26.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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 12:45.
Reply With Quote
  #9 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On