Importing Data to SQL the Hard Way

“Miracle worker” is not part of my job title, but there are times when what I am asked to do, coupled with the limitations placed on me, cause me to fall little short of that level.

At one point in my career, I was asked to import data from many spreadsheets into SQL Server. The catch was that the DBA (database administrator) would not grant me the rights needed to do bulk insert because I was a consultant, and according to company policy, only full-time employees could have those rights.

So there I was, spreadsheets open, with no idea how to put the data into the database. With bulk import not an option, the only way to put the data in would be through T-SQL.

While it is true that Excel is a spreadsheet and was made for handling numbers, it also has excellent text manipulation functions that are often overlooked. I decided to employ the data functions in Excel to generate T-SQL that could then be run in SSMS (SQL Server Management Studio) to insert the data.

Table Value Constructor

In order to get this data easily into T-SQL, we are going to use the table value constructor, also known as the VALUES keyword. This allows us to insert multiple rows into a table variable and work from there.

The basic syntax of this command is:

INSERT INTO <*tablename*> (*field list*) VALUES
(*scalar values*),…(*scalar values*);

Our Sample Spreadsheet

For this example, I have a three-column spreadsheet. The first column (A) is an integer value containing a practice number. The second column (B) is a practice manager in string format. The third column (C) is the practice state, also a string.

We will use the next column (D) to create the T-SQL necessary to create and populate the table variable with the information in the spreadsheet. Once this column D is populated, we can copy and paste straight into T-SQL.

Setting Up the Table Variable

The first task is to declare the table variable that will hold the information.

In Cell D1, the text becomes:

DECLARE @TempTable(PracticeNumber INT PRIMARY KEY, PracticeManager VARCHAR(100), PracticeState VARCHAR(2)) 

DECLARE @TempTable(
    PracticeNumber INT PRIMARY KEY, 
    PracticeManager VARCHAR(100), 
    PracticeState VARCHAR(2)
)

Table variables can only have indices if you create them during the variable declaration, so I have created the primary key at this time.

Setting Up the Insert

The first line of the batch will need to have the INSERT statement. We put this as the start of cell D2:

INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES 

INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES

Setting Up the Values (Data)

The VALUES statement is easy to set up in each row. We are going to construct a text string using formulas for each row.

="(" & TEXT(A3,"#") & ",'" & B3 & "','" & C3 & "'),” 

="(" & TEXT(A3,"#") & ",'" & B3 & "','" & C3 & "'),”

The first thing is the ever-important equals sign to let Excel know it has to do some work. Then we add the open parentheses to bracket this row.

Next, the value is an integer in Excel and will be going into an integer column in the table variable, so we convert the cell in the A column to text using the TEXT function.

Our comma comes next, indicating we move on to another value. This one is a text string, so we have to include the single quotes before and after.

Column C is also text, so we add that with quotes on both sides and then finish everything off with a close parenthesis.

We then add the comma so that we can go on to the next value.

This formula generates text that looks like this:

(628180,'Tony Stark','NJ'),

SQL Limitations

The table value constructer can only import 1,000 records in a batch. This means that every 1,000 records, we need to terminate the previous batch with a semicolon and start with a new INSERT statement.

This requires a bit more text manipulation.

=IF(AND(A1<>"",A2=""),";",IF(MOD(ROW(A2),950) = 2, ";INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES",",") &"(" & TEXT(A2,"#") & ",'" & B2 & "','" & C2 & "')")

=IF(AND(A1<>"",A2=""),";",IF(MOD(ROW(A2),950) = 2, ";INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES",",") &"(" & TEXT(A2,"#") & ",'" & B2 & "','" & C2 & "')")

=IF(AND(A1<>,A2=_),";"...

The start of the formula checks to see if there should be anything in this cell. This IF condition will put the terminating semicolon in for the entire script if there is no data in the current row, but there is data in the previous row.

,IF(MOD(ROW(A2),950) = 2, ";

The first part of the ELSE takes a look at the row number. If the row number is 2 past 950, it will terminate the current statement and start the next one. I use 950 rather than 1000 because it gives more wiggle room with the math.

In Row 2, Row 952, and so on, it will put the start of the INSERT statement:

;INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES"

In other rows, it will add the comma necessary between the list values.

Using the Formula

Once the formula is set, you can copy the formula down in Excel as far as you need to, plus one row to get the final termination.

The spreadsheet output will look like this (please note, I have adjusted the formula to terminate the batch every 6 rows so the output is clear):

Conclusion

Excel is very handy to generate SQL in cases where you don’t have access to bulk insert. By using the text and logic functions, you can generate SQL to make inserting large amounts of data easy.