useful or keep silent !!!

insert data from excel into sql server 2005/2008

Before use this query, you must enable OPENROWSET and OPENDATASOURCE support.

You can enable with open SQL Server Surface Area Configuration Tool and Surface Area Configuration Feature and checklist enable OPENROWSET and OPENDATASOURCE support apply and OK.

Now you can use this query :

insert into Tabel
(field1, field2)
select * from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘excel 8.0;Database=C:\name_excel.xls;HDR=YES’,
‘select * from [Sheet$]’)

You can modified this query like this :

UPDATE b
Set b.field = a.field_excel1
from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\name_excel.xls;HDR=YES’,
‘select * from [Sheet$]’) AS A
INNER JOIN tabel as B on b.field = a.field_excel2

One response

  1. good research!

    April 28, 2012 at 12:25 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s