Excel Data into SQL Server with Copy Paste

 in Database  tagged data / database / excel / sql / microsoft / import / paste / copy by

You can import a table of Excel data into a SQL Server database without having to re-type any of your data. The copy and paste is an easy way that is discussed below. Alternatively you do this by using Excel’s formulas to generate an SQL string. Copy that string into a regular text file and rename the text file to have an sql extension instead of text. Your database should be able to execute that sql file.

This excelinsertintosqlstring Excel file has sample data and the formulas. Please use with caution on a non-production server.

In the Excel file, select rows E25 to E29. Right click to Copy those rows. Open up a text editor and paste. Save the file with an sql extension. Remove the last comma in the file. Check that it is OK. Import it into your database if you are satisfied with the syntax and data.

Copy and Paste

This may be an easier way. You can copy and paste from Excel to a SQL Server table. Here is an example Excel file shown in a screen shot.

Here is your table in SQL Server Management Studio (SSMS). You have already created it. Yes, in this example you must first create the table if it doesn’t already exist. The first column is a primary key and an Identity. In this screen shot you have right-clicked on the table and selected Edit Top 200 Rows. You have then selected the first row. It is important to select the entire first row, as shown.

Here is what SSMS now looks like after you copy and paste. It worked!

The important thing to note is that you must include a sequence number column in your Excel data. That column may contain all ones if you want. It does not need to be 1, 2, 3, 4 and so on. The Identity will handle the proper sequencing for you. Also, you must select the entire first row in SSMS after you choose to edit the top 200 rows. The number of columns in your Excel file must match the number of columns in the SQL Server table.

Identity Remembers

If you delete from the table city to get rid of all of the records and do this procedure again (copy and paste), you will notice the following results. Your numbers in Excel, 1 to 5, are ignored. That brings up the question as to whether or not the values of the numbers matter at all. The table specifies an integer.

Drop and re-create the Table

Let’s drop and re-create the table. Now we will edit top 200 rows and copy and paste in data from Excel that uses some “random” un-ordered numbers. The numbers are ignored as SQL Server uses 1, 2, 3, 4, and 5 because it is an Identity.