You have some data in Excel that you need to import into SQL Server. You don’t have a table in SQL Server yet, so you need to first create a table in SQL Server that can hold the data. If your data is in a “table” format in Excel you can do this in four steps. By “table” format I mean that the columns are named and the data exists right under the columns, as in the screenshot below in cells A1 to D3 (or wherever your table ends). The first three steps will be covered in this article.

Below is a screenshot of our sample data in Excel.

Step 1 – Transpose

Cells A1 to D3 contain our original data. Suppose that we had a lot more data than this and we wanted to avoid typing and making mistakes. We could employ this method and save ourselves a lot of time.

To transpose the data do the steps shown in the screenshot to get the results you see in cells A5 to C8. It’s just a matter of copying and pasting. If you don’t see the transpose icon under the paste options, try selecting a different cell to copy to. Try a cell down the worksheet in the A column. You need to give Excel lots of room to paste the data.

Step 2 – Create SQL Script

The next step is to create our SQL Script. In cells A5 to A8 we have the list of columns that will ultimately be in our SQL table. Create a new spreadsheet. Here is where we will create our script for SQL Server. First, copy and paste the cells A5 to A8 (or A5 to the last cell in the range of columns) into your new spreadsheet.

There are a couple of things to do so that this will work. You need to create Excel columns similar to what you see in the screenshot. Second, you will need to copy in the formulas I used to create the script you see in column D. The screenshot of your second Excel sheet is shown here.

I have added an Amount column to illustrate a different data type other than characters. Also you may have dates stored in your original Excel sheet. If the dates are in the format yyyy-mm-dd then the Date datatype in SQL Server will understand. If not, you may need to do a bit of extra work formatting the date data.

The formula that is in D1 is listed below.

="CREATE TABLE " & A1 & " ("

The formula that is in cell D2 and the cells below, except for the last cell is as follows:

=A2 & " " & B2 & " " & IF(ISBLANK(C2),",","("&C2&"),")

Step 3 – Run the Script

This part is easy. Just copy the script from Excel, paste it into a Query window in SSMS, remove the comma at the end of the second- last row and run it in your non-production server.

Now you may want to create a primary key for your table. You may need to check your data types. You may want to add some contraints. You may want your SQL Server table to have an identity column.

If it was successful, you now have a table you can copy and paste the data into. This is step 4.

Step 4 – Copy and Paste

There is another post at this website that discusses copy data from Excel to SQL Server. Its called Excel to SQL Server. In that post we are using a table with an Identity column. Even if you don’t have an identity column, the copy and paste method still works. In SSMS, right-click the table, select Edit top 200 rows, select the first row of data (it will be NULL). Paste.

Results

Here is a screenshot of your data in a SQL Server table. I didn’t type or copy and paste any of the data to get it from Excel to SQL Server. When would you want to do this. One example of this is when you are using Adobe Acrobat Forms and you are collecting data from outside sources such as customers or clients. They fill out the fillable form and then you export that to a CSV file. Then you import it into Excel. Then you want to import that data into SQL Server. For more information on this process have a look at another post in this site called PDF Forms with Acrobat Pro 9. There are instructions at the bottom of that post.