RE: Import/Export to Excel

Every now and then, I need to send stuff from SQL to Excel....

Apart from using DTS and Import/Export wizard, we can also use this query to export data from SQL Server2000 to Excel and vice versa

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

[Via SQLJunkies Blogs]