SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Mass import into existing table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 09/17/2013 :  15:15:01  Show Profile  Reply with Quote
Hi

I have an existing SQL table that I want to import 300,000 rows into. I have copied the table headers into Excel and added all the rows but constantly getting multiple errors no matter what way I try to import it.

So far tried Import Method from SQL Management Studio, BULK INSERT and SQL Script. What is the easiest way?

If I try this

select *
into Coupon FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\coupon.xls;HDR=YES',
'SELECT * FROM [coupon$]')

I get

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.


Edited by - deanglen on 09/17/2013 15:28:40

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 09/17/2013 :  15:33:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Change the setting in the Registry.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 09/17/2013 :  16:01:02  Show Profile  Reply with Quote
How can I do that?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 09/17/2013 :  16:12:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 09/17/2013 :  16:18:24  Show Profile  Reply with Quote
Tried all that. Nothing works. I installed all 32 bit MS programmes, but still it has the same error.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 09/17/2013 :  16:26:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The easiest way is to use the Import/Export Wizard. Unless you plan to import the rows over and over again?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 09/17/2013 :  16:27:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168190



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 09/17/2013 :  16:29:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.maxxxie.net/2012/07/01/the-joys-of-importing-excel-data-into-sql-2008-r2/


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 09/17/2013 :  17:19:24  Show Profile  Reply with Quote
quote:
Originally posted by deanglen

Tried all that. Nothing works. I installed all 32 bit MS programmes, but still it has the same error.

I have had similar experiences; I have searched through various websites and suggestions, including Pinal's suggestions, and many others. None of them worked for me. I felt rejected, jinxed, and incompetant.

Finally I ended up using SSIS, and now automatically resort to SSIS for most data transfer tasks.

Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 09/18/2013 :  04:30:39  Show Profile  Reply with Quote
I agree. We spend thousands on a microsoft server and then even more on software only for the simple task of exporting Excel to SQL to cause all these issues.

Thanks I'll attempt SSIS

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000