Scenario:You are working as SQL Server Developer, you received data in an Excel file for dbo.Customer tables. You need to load this data into dbo.Customer table. How would you generate Insert Statement for given data in excel so you can load into DEV, QA,UAT and Production.
Solution:There are multiple ways to achieve this requirement. You can use Import/Export Wizard first to load the data into Development environment and then generate insert script from table in SSMS.
If you don't want to load the data into table , you can use Excel formula's to generate Insert statement from data.
|How to generate Insert Statements from Excel for SQL Server Table|
First of all we need to know in which table we want to load the data. In our case it is dbo.Customer. Also the names of columns provided in Excel are exactly the same like our dbo.Customer table columns.
Go to any column and type this formula as shown below in first row. I typed in column G.
="insert into dbo.Customer ("&A1&","&B1&","&C1&","&D1&","&E1&") Values"
In Excel we start formula with = (equal to sign). For concatenation we use &. To add string we have to put double quotes around as we did for comma ",".
|How to generate Insert Statements for SQL Server Table from Excel Data|
Now first part of our Insert statement is ready. we have to generate the values part for our insert statement. Go to 2nd Row and use below formula.
Drag it down to all the way till last record. All Insert values will be generate. Copy all the values from column G and paste into SSMS. There will be extra comma(,) at the end with last record. Remove that and run your insert statement to test if working as expected.
|How to generate Insert Statements from Excel file for SQL Server table|
Here are the generated Insert Statements, copied from G Column and pasted in SSMS.