running about 75000 insert statements

Question

What is the best method to import data from an Excel worksheet? As of now I am use SSMS Express so I don't have access to SQL Import Wizard. I also don't have permissions to execute the BULK INSERT command.

My current workflow is as follow: Clean up the excel file, save as CSV, and import it into a SQLite database. Use an IDE like RazorSQL to generate SQL INSERT statements.

This worked nicely until I hit an Excel file about 75000 rows. SSMS just gives an error saying "query finished with errors" or something like that. No error message is shown. I tried adding GO at the end of each line but I got out of memory error.

What are my options?

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

To answer your question, the best method to import data from excel, in my past experience has been to read excel data into c#, do any clean up and formatting as necessary since excel likes to mess with the data, then use SqlBulkCopy (you only need select/insert permissions) to insert into SQL Server. See this SO answer if you need help reading excel from C#

Update: Given you're not a dev, try using the bcp utility (you should only need select/insert permission)you may need to save the excel file as CSV first, then import it directly into sql server, see this SO answer

edited May 23 '17 at 11:56

Community

1
1

answered Jun 14 '13 at 13:46

Jason

3,844
1
21
40

I don't have Visual Studio as I am not a "IT" department guy. – masfenix Jun 14 '13 at 13:48
@masfenix see the update, you can try saving the excel file as a CSV file, then using bcp utility – Jason Jun 14 '13 at 13:59

score 0 · Answer 2 · answered Jun 14 '13 at 13:47

You can use following:

bcp utility (between file system data dump and database),
OPENQUERY (can be used from SSMS, works between external datasource like Excel/csv and database),
BULK INSERT (can be used from SSMS, works between external file with user-defined structure and database),
SSIS (usually as dtsx package, has its own GUI, works with various souces and destinations)
Set of INSERT statements (all of them one after another, eventually sliced with GO or packed with UNION ALL)
Set of records serialized in XML variable (can be used from SSMS only; you have to serialize/deserialize it by your self using FOR XML and XML functions)

There are surely other possibilities, but these are maybe most used ones.

EDIT: It seems to me that you could try with GO after every 5-10K lines in your script. If that doesn't work, XML serialization/deserialization could be the way to go.

You could be happy with [this](http://sqlfool.com/2008/12/bcp-basics/). However, I can't remember right now, but if you don't have the BULK INSERT permission, not sure if you can use bcp... try and see. — OzrenTkalcecKrznaric, Jun 14 '13 at 13:52

score 0 · Answer 3 · answered Jun 14 '13 at 13:50

0

Could you use a linked server to connect to the Excel Document? How to use Excel with SQL Server linked servers and distributed queries

answered Jun 14 '13 at 13:50

Drew Leffelman

516
2
8

score 0 · Answer 4 · answered Jun 14 '13 at 14:18

0

A quick and dirty workaround: pull the rows in batches of 50k.

select * from employee limit 50000

select * from employee limit 50000, 100000

From http://www.razorsql.com/articles/mysql_limit_query.html

answered Jun 14 '13 at 14:18

Richard

6,215
4
33
48

Of course, that's a workaround to a process that is already a workaround. – Richard Jun 14 '13 at 14:19

running about 75000 insert statements

4 Answers4