Copy paste data from Excel to SQL Server 2005 May 24, 2008Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
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
- 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
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.