join the MSSQLTips community

Today's Site Sponsor


 

SQL Compare quickly and easily compares and synchronizes SQL Server database schemas
 



What aren't you seeing?

Insert data from Excel to SQL Server 2005 by using copy and paste commands

Written By: Kun Lee -- 2/8/2008 -- read/post comments -- print -- Bookmark and Share

Rating: Rate

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 with in SQL Server 2005, are any new tricks available to streamline the Excel to SQL Server loading process?

Solution
Yes - 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 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'


 

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


 

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


 

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


 

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


 

Next Steps

Readers Who Read This Tip Also Read Free Live Webcast Comment or Ask Questions About This Tip


Sponsor Information
Free SQL Server performance monitoring dashboard – Idera SQL check

Realistic test data in just one click with SQL Data Generator.

We fill in the gaps... SQL Server Training, Development, Performance Tuning, SSIS and more

Attend a SQL Server Conference for Free

Free white paper - Simplify SQL Server Management: Helpful SQL Server Tips


Get Our Tips Newsletter

We keep 50,000+ SQL Server professionals informed.



Red Gate Software - SQL Prompt

How can he write SQL so fast? Some developers write SQL amazingly fast. Do you want to know their secret? It’s SQL Prompt. “This is a must-have tool for all T-SQL developers.” Brian Brewder, Brian Online.

Download now!

More SQL Server Tools
SQL Prompt

SQL compliance manager

SQL comparison toolset

SQL Compare

SQL diagnostic manager




Copyright (c) 2006-2010 Edgewood Solutions, LLC All rights reserved
privacy statement | disclaimer | copyright | advertise | write for mssqltips | feedback | about
Some names and products listed are the registered trademarks of their respective owners.


CareerQandA.com | MSSharePointTips.com | MSSQLTips.com