Mass import into existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mass import into existing table

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-09-17 : 15:15:01

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 15:33:54

Change the setting in the Registry.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-09-17 : 16:01:02

How can I do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 16:12:16

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

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-09-17 : 16:18:24

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 16:26:11

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 16:27:37

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168190

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 16:29:19

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 17:19:24

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.

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-09-18 : 04:30:39

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources