jump to navigation

Copy paste data from Excel to SQL Server 2005 May 24, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
trackback

Lots of people have their data in MS Excel and manipulate their data with Excel. Many times we need to load data from Excel to SQL Server because we need to perform tasks on our data and the tools that MS Excel provides do not suffice. We can do that with various ways

  • DTS
  • BCP
  • OPENROWSET
  • Import\Export Wizard

Sometimes this can be a time consuming task.  SQL Server 2005 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 assume we have a database called ‘Example’ with a table called ‘Description’ consisting of two columns (Name, Surname).  We also have an Excel worksheet where you want to load data directly from Excel to SQL Server. 

Let’s create the database first. We fire up SQL Server 2005 and we open a new query window.

CREATE DATABASE Example
GO
CREATE TABLE [dbo].[Description](
      [Name] [varchar](100) NOT NULL,
      [Surname] [varchar](100) NOT NULL)

In the following image we have the data we need to load from Excel to SQL Server.

We follow the following procedure

1) We copy the data from Ms Excel

2) We go back to the Management Studio and select the table Description and then Right-Click and choose “Open Table”

3) Left-Click on the top left corner of the window that will appear. the whole table will be highlighted and then just click “Paste”

The following image shows the data copied from Excel into SQL SERVER.

About these ads

Comments»

1. SQL_BEG - August 11, 2009

When I try to paste the “Paste” option is greyed out. Is there an option or something that needs to be activated? Thanks.

2. aaronls - November 29, 2010

You have to make sure you do not have any data entry active in a cell currently. In other words, if there is a row with a little pencil symbol indicating you are editing or adding a row, then you need to hit escape to cancel that, and then right click the top left corner again. Note that you do not click the top left cell, but actually go up and left of the header to the little gray area that appears to the left of the first header and right click there. I had no idea this capability existed. Definitely a great tip.

Nettkiya Joseph - February 28, 2011

I am still unable to paste although there is no row marked for editing.

3. Rajesh - June 17, 2011

1. try you has all the rights to paste in the table(insertion rights)
2. if yes and then the data is not getting pasted, then paste the same in a notepad and from the notepad select all and copy and then paste it in the table. you have to select the last row where all the column values will be NULL and then try pasting the same.

4. Gavin - July 7, 2011

If your db table has an ID primary key column before “Name” then add an empty column in your excel sheet before “Name” and select it as welll. Otherwise the paste will fail as you can’t insert into an ID field (cos it updates itself when an entry is added).

5. Munchman - July 12, 2011

Additionally, If you already have records in the table you need to right click on the left hand greyed out row indicator of the window (grid) for the next available row and paste from there. As Gavin mentions don’t forget to leave an empty cell for an ID/PK if you’re table has such a field. Also be sure your table is set up for your ID to auto index for the next record if applicable.

6. Paartha - March 31, 2012

Is it possible if a use a C# interface where the datagrid enables us to copy paste data

7. Paartha - March 31, 2012

I meant to say that if is it possible to import data into sql server using C# client using datagrid


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,788 other followers

%d bloggers like this: