Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.

I would like to use BULK INSERT, but I have no access to the db server file system.

Questions

  • is there a way to pass/upload a remote file to the Sql Server in order to use the BULK INSERT?

  • is there any other tsql way to import data from file to db?

share|improve this question
    
Can you just make a quick integration services package for that? –  Zane Oct 16 '12 at 14:48
    
@Zane. Can you give some more details? –  ADC Oct 16 '12 at 15:09
    
I'm going to be in and out of meetings for the next hour or so if you need more help I can go into detail once I'm back from that. Basically what you want to do is create an integration services package. In that package you want to create a data flow task. In that Dataflow task create a link from a flat file location to a OLEDB connection. If you need more help let me know and I will post details on how this is accomplished. –  Zane Oct 16 '12 at 16:22
    
@Zane. Thanks for the details. It is quite clear. –  ADC Oct 16 '12 at 16:26
    
One last thing in order to get bulk insert you want your Data access mode on your OLEDB connection to be Table or view - Fast Load Turn off table lock and I usually find 10,000 Rows per batch works well for most situations. –  Zane Oct 16 '12 at 16:31
add comment

1 Answer

up vote 1 down vote accepted

If you have one of the system administrators or database administrators create a read/write share on the server and grant your domain account access to the share, then you should be able to copy the data up and then use BULK INSERT because it would run in the context of the SQL Server service account which would be able to see the path. Alternatively, you could make a request that they place the data file on the server for you and give you the local path which you would then use in your BULK INSERT script.

You could also use the SQL Server Import/Export wizard to create a connection to your file as the source on your workstation then create a connection to the server as the destination and import the file that way. You can get to the Import/Export wizard by right clicking the database in Object Explorer and selecting Tasks > Import Data.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.