Systems Engineering and RDBMS

Simple Copy/Paste to load data from Excel in SQL Server

Posted by decipherinfosys on February 25, 2008

We have blogged in the past about the data extraction and loading (exports and imports) methods in Oracle, SQL Server and DB2 LUW and the pros and cons of those different methods in terms of performance as well as maintenance etc.. At times during the QA or Development process, there is a need to load some data quickly for test cases. Under such circumstances, going through the scripts or the wizards could be a time consuming thing for users who are not that technical. Here is an alternative option that is available in SQL Server Management Studio (SSMS) in SQL Server 2005.

Say that we have a table called T1 with this structure:

USE [DECIPHER_TEST]
GO
/****** Object: Table [dbo].[T1] Script Date: 02/24/2008 12:00:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [int] NULL
) ON [PRIMARY]

And in this table, the end user wants to load up 10 records which he/she has already put in an excel spreadsheet:

ssms_1.jpg

You can just highlight that data and hit Ctrl-C to copy that data into the buffer. After that, open up SQL Server Management Studio and right click the table and select open:

ssms_2.jpg

Now, click on the top left corner most tab to select the data and hit Ctrl-V to paste the records or just right click on that tab and select paste:

ssms_3.jpg

And it will paste the data from the buffer into the table. Once done, you can just exit out and it will save the data into the table:

ssms_4.jpg

This is just a quick copy/paste method of getting the data into the table in SQL Server 2005.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: