Import Excel data into SQL Server using copy and paste

By:   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | 5 | > Microsoft Excel Integration


Problem

Loading data from SQL Server to Excel is a common practice in many automated and ad-hoc processes completed by DBAs on a daily basis.  Traditionally loading data from Excel to SQL Server has been completed by DTS, SSIS, BCP, OPENROWSET, Import\Export Wizard, etc.  Unfortunately, for a simple ad-hoc process this can be a time consuming task with a fair amount of clicks and\or coding.  With all of the new features in SSMS, are any new tricks available to streamline the Excel to SQL Server loading process?

Solution

Yes - SQL Server Management Studio provides the ability to copy and paste columns directly from Excel to SQL Server when the table and column names match up.  Let's walk through a simple example.  Assume you have a database called 'Test' with a table called 'MyTable' consisting of two columns (MyID, MyDesc).  In addition, you have an Excel worksheet where you want to load data directly from Excel to SQL Server.  Let's walk through setting up and testing that scenario.

Step 1 - Create the Database and Table

 CREATE DATABASE Test 
GO 

CREATE TABLE [dbo].[MyTable]( 
      [MyID] [int] NOT NULL, 
      [MyDesc] [varchar](100) NOT NULL) 

Step 2 - Open the Excel worksheet, select data only and copy the data 'Ctrl +C'

select destination

Step 3 - Open SQL Server Management Studio and navigate to the table

my table

Step 4 - Right click on dbo.MyTable, choose 'Open Table' then left click on below area

management studio

Step 5 - The area below will be highlighted, then right click and choose 'Paste'

management studio

Step 6 - Validate the data will be inserted into the table

object explorer
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Sunday, June 12, 2016 - 2:47:06 AM - arun Back To Top (41667)

 how can I export data from sql data base to my own xl sheet automatically using code? please suggest:)

 

 


Wednesday, October 22, 2014 - 7:19:15 AM - Alex Back To Top (35033)

Thanks for the tip.

There is a way that requires fewer steps though, I found a couple of plugins that allow 1-click table upload to database (e.g. SaveToDB) for free.

In this example, all you need is connect to Test, load dbo.MyTable, add values and press Save. Awesome!


Friday, November 29, 2013 - 7:01:52 AM - Bhakti Back To Top (27637)

hi..

thanks alot...

its working properly..


Tuesday, November 12, 2013 - 2:01:39 PM - Hans Back To Top (27468)

Gracias

Me ayudo de mucho

SAludos


Wednesday, October 9, 2013 - 9:00:50 PM - Bala Back To Top (27096)

Thanks! Very helpful tip!


Thursday, August 8, 2013 - 6:28:45 AM - chran Back To Top (26144)

Its simply super.... great thanks for the wonder snippet.. mate..


Friday, June 7, 2013 - 8:59:27 PM - Lê Hữu Vinh Back To Top (25348)

thanks. you are good


Tuesday, December 4, 2012 - 3:23:40 PM - Madhuri Back To Top (20742)

Hi,

I tried the above steps to copy paste data from excel. I am getting the following error:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.

The data in row 17 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.

The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s).

**********************

I checked the microsoft support forum and it says that:

 This issue occurs if the following conditions are true:

  • The table contains one or more columns of the text or ntext data type.

To work around this issue, create a new query window in SQL Server Management Studio. Then, run a SQL UPDATE statement to update the row in the table.

 

***********************

 

Can you suggest what to do when the data is of type "text".

 

Thanks,

Madhuri

 

 


Wednesday, August 29, 2012 - 9:02:38 PM - Guillermo Back To Top (19292)

 

Thanks


Tuesday, July 24, 2012 - 2:07:45 AM - Bryan Bailey Back To Top (18761)

You can use this method with success, but it's not exactly an intuitive process for the light-intermediate Excel user.  XLhub  is a powerful Excel add-in that will easily import Excel data into SQL Server using a wizard driven process that guides you through, step by step.  After you've tied your spreadsheet to a SQL server database, XLhub also allows multi-user access and live edits -- thus tracking each user edits so you know who's done what. You can learn more by checking out their website at www.xlhub.com


Tuesday, July 3, 2012 - 10:40:58 AM - richard Back To Top (18321)

rafat - [url=http:\\leansoftware.net]Excel to database[url] will validate & upload 2000 rows in approx 6 seconds, depending on how many columns and current load on the database. It is free to try.

 


Saturday, June 30, 2012 - 5:04:34 PM - rafat Back To Top (18279)

it is a way to import data from excel file to sql database when the the date on the excel file is littel(100 - 2000) records , but when it up to 2000 it a problem ,it is take several minuet's  ,,,, what is the solution in this case ??


Friday, June 22, 2012 - 7:09:45 PM - Bandita Pradhan Back To Top (18180)

Thank You, really helpful.


Wednesday, June 13, 2012 - 7:20:56 AM - richard Back To Top (17964)

Excel to database tool includes validation of data, multiple task setup and sending the data to a stored procedure!

The software also allows you quickly create a template into which you can paste data.

Templates can be reused and even shared accross a network.

Works fine with SQL Server, Access, MySQL and other databases too.

Well worth a look

Excel to database tool

 


Tuesday, June 12, 2012 - 8:36:43 PM - Saba Back To Top (17950)

Thanks you so much.......simple and painless...Thanks again


Sunday, June 3, 2012 - 8:24:10 AM - Adi Back To Top (17789)

Here's another option, which works great for even more complex data -

It's called ClipTable - a free tool that instantly turns any clipboard data into a SQL Table.

You can find it here - http://www.doccolabs.com/products_cliptable.html

 


Thursday, May 17, 2012 - 11:32:19 AM - Kun Lee Back To Top (17502)

Thank you for the feedback Sumanta..

 

Ashish,

I am sorry for late response for your question. I really can't answer your question because I don't know what your skill level is and also  SQL server is way to broad so that topic that you mentioned is not that easy to cover in a few books though.

If you haven't, I highly recommend going SQL Server user group and ask those questions there and there should be a good people can help you to go to right direction. You can google it  "sql server user group" and pick the local area. If you leave washington DC area, let me know. I can meet you there at the NOVA SQL User group and can give you some recommendation based on my experience.

 

Personally, I love books from WROX like

http://www.amazon.com/Professional-Microsoft-Server-Administration-Programmer/dp/0470247967

And also I like this book too.

http://www.amazon.com/Microsoft-Server-Administrators-Pocket-Consultant/dp/073562738X/ref=sr_1_1?s=books&ie=UTF8&qid=1337268675&sr=1-1

 

But that is just administrative part and there is a lot more that I like about SQL books...

 

I hope you have a great time learning SQL!!

 

 


Wednesday, May 16, 2012 - 2:24:39 AM - Sumanta Back To Top (17469)

Thank you very much. It is working fine in SQL Server 2008.

 


Tuesday, May 8, 2012 - 8:04:30 AM - Ashish Gupta Back To Top (17336)

I Am still waiting for answer.please help me.

Thnks in Advance

 

 Warm Reg.


Ashish Gupta


Wednesday, May 2, 2012 - 7:44:48 AM - Ashish Gupta Back To Top (17230)

HI

I want to learn SQL Server 2005 / 2008. please provide me information regarding level of SQL Server and any perfect book. i am very confused. i want to know difference between DB2 / DKB / DBA / Warehousing and Sql Programmer. Please clarify. and mail me some good books of SQL. My id is : [email protected].

Thanks in Advance



Warm Reg.


Ashish Gupta


Thursday, March 15, 2012 - 11:16:39 AM - Kun Lee Back To Top (16409)

Hi Pravin Wade,

 

I am a little confused about what you asking though. If you want to insert some columns data in your sql table and leave rest of them as NULL, you can just make your excel format to match with it. If you have existing data and fill up some columes, that I can't think any easy way to do so in that case, I will just create a stage table to have data that you want to fill up and use typical update statements by using the stage table that you just created.

I hope that answers to your question.

Regards,

Kun


Wednesday, March 14, 2012 - 6:17:41 AM - PRAVIN WADE Back To Top (16379)

Hi,

I want to insert only some columns data in my sql table. is it possible to insert the limited column data or copy paste it.

Please reply.

 

thanks,

Pravin Wade

Learn <a href="www.welcomeconsultancy.in">What is Sharepoint</a>


Thursday, May 19, 2011 - 7:35:28 AM - John Back To Top (13865)

Excellent! Atleast someone knows how to do this without writing code that reads excel documents and then writes it to a database.. >_> that's just plain dumb. Thanks for this ;)


Tuesday, January 4, 2011 - 9:30:51 PM - Ixchel Ornelas Back To Top (12494)

thanks :D!


Wednesday, August 11, 2010 - 4:16:24 AM - Kun Lee Back To Top (10032)
Yes.

Please keep in mind that, this is not going to be as fast as BCP though. Not even close to that speed. So, I'd recommend using small set of data to get it done quickly.

Regards,

Kun


Wednesday, August 11, 2010 - 2:22:45 AM - Ashish Back To Top (10031)
Hi,

 

Is this feature also available in SQL 2008?

 

Regards,

Ashish















get free sql tips
agree to terms