Other ways of doing bulk insert?

Posted on 2008-11-09
Medium Priority
Last Modified: 2012-05-05
My hosting service has turned off bulk insert so I get this error if I try to run the following script:

Msg 4834, Level 16, State 4, Line 16
You do not have permission to use the bulk load statement.

Their suggestion is to backup the db to my local machine, do the insert and restore to the remote.  Is this really feasible or is there a better way?
create table #temp
symbolid int,
transactiondate datetime,
closingprice money
insert #temp
from 'O:\sso.csv'
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
insert into historicalprices.history (symbolid, transactiondate, closingprice)
select symbolid, transactiondate, closingprice
from #temp
drop table #temp

Open in new window

Question by:brettr
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22917178
if fear that is the only option, apart if you wanted to generate the INSERT statements themselves on your local db, and run them afterwards on the prod db

Author Comment

ID: 22917251
How do I generate the insert statements as you mentioned?
LVL 71

Assisted Solution

Qlemo earned 1000 total points
ID: 22917322
You could try with http://sqldump.sourceforge.net/

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question