To Import data from excel to Sql table using SQL Import/Export tool is simple and very effective. But we can import large tables into Sql table by generating insert statements in excel sheet. Here is the trick that i found very interesting and faster than Import/Export wizard.
This approach also requires the destination table to be created manually or it should already exist.
- Run the below script to create destination table and insert some data in it.
The result of last Query is :
- The source Excel file contains below data that we want to import in Test1 table:
- Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts ” Insert Into Test1 VALUES (‘ “, ” ” “,” ‘ ” etc. as shown below:
- Then you need to concatenate these columns using Excel’s CONCATENATE function:
- Finally, copy the same formula to all rows, these are the INSERT statements you need!:
- Now copy them to SSMS and execute them. We have now imported the Excel file data into table :).