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.
| Author |
Topic |
|
vimalraj
Starting Member
5 Posts |
 Posted - 2007-09-11 : 01:39:02
|
| Hi,I need to import data from more than 10 excels having the same format in to a single sql server table.I tried to use INSERT INTO MyTempTable SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 11.0;Database=C:\Book1.xls', [Sheet1$])but got the below errorAd hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.If DTS package is used then I am not sure how I can place 10 excels at a time so that they can be picked one by one and data is imported in to table.Please help.Thanks,Vimalraj |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 05:42:39
|
| you need to create a linked serverorvia dts, you can pick the xls worksheet one by one and choose which tables they are to be inserted into... try it out first and see if it doesn't answer your requirements--------------------keeping it simple... |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
vimalraj
Starting Member
5 Posts |
Posted - 2007-09-11 : 06:40:54
|
| Please tell me How can I create a linked server? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-11 : 06:43:28
|
quote:
Originally posted by vimalraj
Please tell me How can I create a linked server?
Read about Sp_addLinkedServer in sql server help fileMadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 06:52:26
|
| if it's just one time.. just use import via DTS wizardotherwise you need to research and figure out if linkedserver is your solution... read more on it before deciding you need linkedservers...also you should really consult your DBA--------------------keeping it simple... |
|
|
|
|
|
|
Import Excel Data to a sql server table