This post will cover the details of creating insert scripts for SQL Server from the excel sheet without having to write the INSERT statements individually line by line.
We will first create a table named STUDENTS in SQL Server.
CREATE TABLE STUDENTS
(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
PRIMARY KEY (ID)
);
In the next steps we will open a new sheet in Excel and add the columns in the table along with few sample record.
Now the next step is the most important one. We need to carefully add the text to generate the scripts from excel
We need to click on the column E of the first data row and type the below mentioned text in the formula bar.
="INSERT INTO STUDENTS VALUES ("&A2&",'"&B2&"',"&C2&",'"&D2&"')"
Here we have written a simple sql insert script but the only difference is that instead of having the actual values for the columns we have used the reference of the cells in excel.
Mentioned below is the format in which we need to enter the text in the formula field based on the data type.
Column Name Formula Text Format Comment
ID “&A2&” No Single Quotes (‘). Same rule would apply to AGE field.
NAME ‘”&B2&”’ The formula text is inside single quotes since the NAME field is varchar.
Same rule would apply to ADDRESS field.
We can also use sql formulas like GETDATE() to include the CreatedDate or a hardcoded text like ‘Script’ for the CreatedUser in case we want to capture more details in the table.
To generate the insert script for all the rows we can drag the first row until the last row.
Once the script is generated we can copy it and run it in SQL Server to insert rows into the table. This example demonstrates the generation of Insert scripts but can can generate any data specific script with the similar technique.
Hope this post is helpful in generating scripts from excel. These are some of the handy techniques that saves time and also inserts accurate data into the table.
Updates as on April 27, 2023
When you have a date or datetime value in the excel then you might notice the format of the generated
script through excel has the date format changed to number.
You can use the TEXT function in excel to resolve this issue.
The excel formula
="INSERT INTO EMPLOYEE VALUES('"&A1&"')"gives the result as
INSERT INTO EMPLOYEE VALUES('45043')when actually the cell A1 in excel contains 27-04-2023
You can resolve this by modifying the formula in excel as
="INSERT INTO EMPLOYEE VALUES('"&TEXT(A2,"yyyy/MM/dd")&"')"You can use any format of your choice to get the result. So basically what we have done is placed the excel TEXT(value,format_text) inside & in the existing formula.
Handling NULL values
When you want to enter NULL value while generating the script make sure that you do not put
NULL inside 'NULL'(Single Quotes) as this would generate the script with NULL string value and
not DB NULL value.
-- Incorrect syntax
="INSERT INTO EMPLOYEE VALUES('NULL')" --here you might actually put some column '&A1&' instead of 'NULL'
-- Correct syntax
="INSERT INTO EMPLOYEE VALUES(NULL)" --here you should put &A1& instead of NULL