How To Use Excel To Create Large SQL Insert Statements

When adding records to a SQL database, data is often given in the format of an excel spreadsheet and it is up to the user to import the data into the database. It is impractical and time consuming to update an individual insert statement for each record. Instead, use excel as a resource to quickly and simply create a series of Insert statements which will also minimise human error.

When adding records to a SQL database, simple 'Insert Into' statements can be used. This will add one record per Insert statement. It may be the case that there is no user-friendly import system set up and the records must be entered manually with this method. It is not always the case that records are received one at a time. Sometimes a large amount of data is given all at once, particularly if the database is in the initial set up stage. Data is often given in the format of an excel spreadsheet and it is up to the user to import the data into the database. It would be impractical and time consuming to update an individual insert statement for each record. Instead it is possible to use excel as a resource to quickly and simply create a series of Insert statements which will also minimise human error.

In order to begin, work out the Insert statement required for the first record. As an example, let us assume we are entering a contact record into a database from a spreadsheet containing three separate columns for FirstName, Surname and PhoneNumber. In this example the Insert statement into Table_Contact is as follows:

INSERT INTO Table_Contact VALUES ('Sarah', 'Hanson', '5556543')

In order to create the insert statement for all the records on the spreadsheet, add an additional column formatted as text with the following value in each cell:

INSERT INTO Table_Contact VALUES ('

The clever part of the Excel sheet is the Concatenation function. This function allows you to merge together any cells along with text of your choice. It can join up to 30 strings together. The advantage of this is that it can automatically join together all the strings along with the SQL script to give the final full SQL insert statement. The user can then simply copy the final script into the SQL Query Analyser and press play to run it. If you were to use the formula editor to enter the following Concatenation function into a separate cell it would look as follows:

=CONCATENATE(A2,B2,C2,D2)

The result would be as follows:

INSERT INTO Table_Contact VALUES ('SarahHanson5556543

Since there is no separate column to include the quotation marks, spaces and commas required, excel has merged them altogether as requested which would result in a SQL error. There are two ways to deal with this issue. The first is to add columns and enter the missing characters as appropriate. This is often the simplest solution as it avoids complications in the formula and the user can clearly see what is to be merged. Fortunately Excel Concatenation provides a simpler answer for those who are willing to give it a go. You can enter these characters directly into the formula editor. Change the formula as follows:

=CONCATENATE(A2,B2,"', '",C2,"', ","'",D2,"')")

The cell with the formula should be formatted as General and the final result should be the complete and correct SQL Insert Into statement. This formula can simply be dragged down to all required rows in the column. Since dragging the formula will automatically update it to include the appropriate cells, you should be left with an individual Insert statement for each row including the correct information. This series of statements can simply be copied and pasted into the SQL Query Analyser. It is always worth taking a full copy of the database to use as a test database so you can test the scripts first. Possible problems may occur if you have names such as O'Hanson with an apostrophe. This may have to be manually adjusted.

Excel is an excellent resource for creating large SQL statements from data already in an excel spreadsheet. The example used here is very simple and the function may need refining for different data formats and other issues. Once the user is confident and fully understands the method it can be further improved by removing the column with the initial part of the Insert statement and simply adding this as text to the concatenation formula. The formula would then look as follows:

=CONCATENATE("INSERT INTO Table_Contact VALUES ('",B2,"', '",C2,"', ","'",D2,"')")

If there are huge amounts of data it may even be necessary to set up two concatenation functions with half the SQL Insert statement in each one. It is then possible to set up a separate concatenation function to merge them both. This will all depend on the level and understanding of the user and the type and amount of data.