Inserting excel sheet data in to SQL Server 2005 database table
Posted by ganta pothuluri reddy on April 30, 2009
1. First of all see that you have the create permissions in that database.
2. If you have the Create Permissions, Go ahead otherwise get it from DBA
3. Try to create a temporary table with the column names of the excel sheet.
- Example:
create table Temp
(
Column1 datatype null/not null,
Column2 datatype null/not null,
Column3 datatype null/not null,
Column4 datatype null/not null,
……………………… ……… like on
)
4. After creating the Temporary table Insert the excel sheet with data in to the table.
INSERT INTO Temp
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\(NAME OF THE EXCEL SHEET).xls;HDR=YES;IMEX=1′, ‘SELECT * FROM [sheet1$]‘)
GO
5. Be sure that the sheet number of the excel sheet is mentioned correctly, here it is Sheet1 but It might be different in your excel sheet and save the excel sheet in C drive.
That’s it you can see the excel sheet data in table Temp.
