Insert data from Excel to SQL Server

Here I am discussing about data insertion from MS Excel into SQL Server table. SSMS provides  Export Import wizard by which you can achieve same easily. Follow below steps

Suppose I have a Excel file on server as c:/fis.xlsx.

1.       Go to database

2.       Right Click on database select tasks

3.       Select Import Data

                  

4. A SQL Server Export Import wizard appears.Click Next.
   
   
5. Choose DataSource Microsoft Excel from the List and Browse the Excel File
   
   
   

6. Click Next.
   
   
7. Choose destination.
   

8. Click Next
   
   
   
   
   
   
   
   
   
9. Next
   
   

10. Click Next->Next and Finish.
    
    
    
    
    
    
    
11. Finally a new table named Sheet1$ created.
    
    
12. Now if you want to insert these records in existing table,you can use simple insert statement and drop table.
    
    

--Sample

insert into dbo.existingtable(col1,col2,col3) values
(select col1,col2,col3 from dbo.Sheet1$)

Example:

insert into dbo.FileRecord(FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location)
select top 2 [FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location from dbo.Sheet1$

13. Your data now successfully inserted into SQL Server table from Excel.
    
    
14. You can drop the table.
    
    
15. drop table dbo.Sheet1$