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 |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2008-11-13 : 21:25:12
|
| Can anyone tell me how to import a table to SQL from excel? |
|
|
Jason100
Starting Member
34 Posts |
Posted - 2008-11-13 : 21:33:09
|
| select * into TABLE from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=C:\your.xls','select * from [Sheet1 $]') |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 23:05:43
|
| or use export import wizrd. |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2008-11-14 : 06:12:40
|
how do you get to the import wiazard? I can't seem to find it....quote:
Originally posted by visakh16
or use export import wizrd.
|
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-14 : 06:26:06
|
| it'll be in the installtion directory somewhere, or windows statrt--> all programs-->microsoft sql server-->import/export wizard.Or tyou could launch it by right click on a table/view in enterprise manager and clicking on export. |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2008-11-14 : 06:51:24
|
I get this error when I try to import data to a table""sg 15281, Level 16, State 1, Line 1SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."It seems that my sa rights need to change, can I do this?quote:
Originally posted by visakh16
or use export import wizrd.
|
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-14 : 07:05:28
|
| I think its disabled by default. You'll need to enable the option to use openrowset. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-14 : 07:10:40
|
quote:
Originally posted by jcb267
I get this error when I try to import data to a table""sg 15281, Level 16, State 1, Line 1SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."It seems that my sa rights need to change, can I do this?quote:
Originally posted by visakh16
or use export import wizrd.
you need to go to sql server 2005-> configuration tools->sql server configuration manager select features and enable 'Ad Hoc Distributed Queries' option for using them. they are disabled by default.Alternatively you could find export import wizard by connecting to db and right clicking and selecting tasks->export data or import data. |
|
|
|
|
|
|
|
|
Import Table