Tuesday, March 31, 2009

import data from ACCESS/EXCEL to SQLSERVER

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
ACCESS:
select * from openrowset('Microsoft.Jet.OLEDB.4.0',
'd:\orgmst.mdb';'ADMIN';'','SELECT * FROM org_mst')

EXCEL:
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Excel 8.0;hdr=yes;database=D:\orgmst.xls',ORG_MST$)

No comments: