bulk insert

amitabh04
amitabh04 used Ask the Experts™
on
Hi,
 
1) I am trying to execute the following in query analyzer. The file c:\converted.txt exists on the server. I am actually logged on the server but get error message "Cannot bulk load. The file "c:\converted.txt" does not exist."


BULK INSERT winpayuncashed FROM 'c:\converted.txt'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

2) How can I file path as parameter in the command.

Thanks a lot for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Don't believe you can do that with the filename, without dynamic sql.  Check this out:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23037605.html
Top Expert 2008

Commented:
Hello there,

to use the filepath as a parameter you would have to create a stored procedure for the bulk import like this:

Create Procedure dbo.bulkImportwinpayuncashed(@FilePath varchar(300))

as

BULK INSERT winpayuncashed FROM ''' +@FilePath+'''
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);

Then you should be able to run the following command:

exec bulkImportwinpayuncashed "C:\converted.txt"

Author

Commented:
Hi,
  Still get error message "Cannot bulk load. The file "c:\converted.txt" does not exist"

Thanks a lot.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2008

Commented:
Is the user running this script able to access the location where the file is stored? Most of the times, SQL is running under a system account or a different account than the user logged on to the server, so maybe it's a simple permission issue? Can you try to put the converted.txt file into the same directory where your SQL database resides? And then just use the path "converted.txt" without any absolute path names? It should try to read the file from within the SQL data directory then.
Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
Please make sure path is of the SQL Server system not any other system.

Author

Commented:
Hi,
  What you mean "path is of the SQL Server system " and not other system?

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial