Wednesday 11 September 2013

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


                  

  1. A SQL Server Export Import wizard appears.Click Next.

  2. Choose DataSource Microsoft Excel from the List and Browse the Excel File



  1. Click Next.

  2. Choose destination.


  1. Click Next




  2. Next


  1. Click Next->Next and Finish.



  2. Finally a new table named Sheet1$ created.

  3. 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$



  1. Your data now successfully inserted into SQL Server table from Excel.

  2. You can drop the table.

  3. drop table dbo.Sheet1$


5 comments:

  1. is there a way to do this using automation rather than the wizard? What's going on behind the scenes? I see so many tools to extract data from Excel (or save it to Excel), something native to SQL Server would be great!

    ReplyDelete
    Replies
    1. Yes,We can achieve the same using OPENROWSET
      as
      Select * INTO sqlServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=D:\files.xls;HDR=YES',
      'SELECT * FROM [Sheet1$]')

      Delete
    2. Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

      i am getting this error while inserting data

      Delete
    3. Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

      i am getting this error while inserting

      Delete
    4. Are you trying this using wizard as I post or by using query.
      if query then plz share your query?

      Delete

Please leave a comment for this post