Loading database content into MS SQL database

I am looking for a better hosting provider for my website. The key feature of my website is that it provides a large amount of research information. My old site is mostly just the pages (10's of thousands of them) that contain the information. But I want to find a hosting provider with which it will be easy to take the database I maintain offline for compiling the research and loading that data into a database on the site. I use MSSQL. However, I have lots of tables and some have tens or even hundreds of tousands of rows. SoI need to find a provider that has the capability for me to load the database in bulk either via restoring the DB from my own DB created not on the site or other bulk approaches that do NOT involve having to export CSV files on my machine and upload and run in the CSV files. That approach for this much data would make database updates a cmplete non-starter (why I want to leave my current hosting provider - because until my DB is online, I can't provide a good search feature into my research data.) Do you have the necessary tools/capabilities for this? Ideally, I would want to simply create backup on my machine, upload the backup file to my hosting location, and then tell MS SQL on the site to restore the backup, of course with the typical MS SQL setting that says it is ok it was backued up from a different DB. But any technique that is no more time consuming than that would be acceptable.

 

How to load data from an XML file

On the same topic...

My site requires that I periodically (2 or 3 times a week) delete and then insert several hundred records into the MS SQL database. I receive the data to be inserted as an XML file with a schema matching my database tables.

Since BULK INSERT is not allowed, how can I insert records from an XML file into the MS SQL database?

Thanks,
yitz

 

I'm not sure what you mean. How will a script help to insert data from an XML file without BULK Insert.?

I currently have a stored procedure that uses this technique for reading the XML file:

Code:

 DECLARE @xmltable table
 (
     xmlfield XML
 );

 SET @SQL = 'SELECT * FROM OPENROWSET 
     ([B]BULK [/B]''' +  @XMLFilePath + ''', SINGLE_CLOB) AS XMLDATA';
  ...
 INSERT @xmltable EXEC (@SQL);

and from there I insert the data into the Table using INSERT INTO ... SELECT.

I get a permissions error when trying to run this. How can I get the XML data inserted into a table without having to issue hundreds of individual INSERT statements?