Uploading Data - SQL Server Forums

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

Uploading Data

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-04-15 : 07:47:58

Dear All,

I have few excel and i want to upload that in to sql server 2008 r2 db without using imp/exp wiz.

Could any one tell me how to do this.Any sample scrip is highly appreciated.

Reagrds,
SG

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 07:50:29

Post your some sample data what is in excel and which table you wanna to store

And don't create duplicate threads

Raghu' S

satheesh
Posting Yak Master

152 Posts

Posted - 2011-04-15 : 08:06:22

Sorry Raghu,i understood what you mean.I posted that in wrong fourm by mistake.that's y i posted it again.I will make sure next time.

Sample data in excel

cstid name amount postcode status
123 xxx 23$ xyxyx active

i want to store this temp table when uploading data.

Regards,
SG

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 09:12:02

Ok here is solution
You need to save data in excel file and here i used C:\ you can change where is you file here I used 2003 office cant support 2010 ok try
In excel you need to put any where data but Header column name is same only once
cstid name amount postcode status
123 xxx 23$ xyxyx active
124 xxx 23$ xyxyx active
125 xxx 23$ xyxyx active

save this file as Demo.xls and save as offic 97-2003 and store at C:next run this statement
Note: I am using MS sql 2005 so it can be run on 2008 also

SELECT * into #temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\demo.xls', [Sheet1$])

Raghu' S

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 09:19:53

Use this for only see excel data it wont insert into temp table
and even this works on office 2010
and extension is xlsx ok
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\Demo.xlsx', [Sheet1$])

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources