SQL Server Portal

Problem :
Maintaining format is sometimes very critical when data is copied from one source to another. Yesterday when I was working on an excel report I faced this problem.  When I copied a formatted ID (“0001“) with leading zeros from MS SQL Server and pasted it in MS Excel it became 1 only.

Solution :

Lets Fix it step by step :

Step 1 :
Let me create a sample to demonstrate the error.

USE tempdb
GO
CREATE TABLE [dbo].[tbl_Employee](
	[Employee ID] [varchar](5) NULL,
	[Employee Name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00001', N'A. Scott')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00002', N'Alan')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00003', N'Alejandro')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00004', N'Alex')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00005', N'Alice')
GO
SELECT [Employee ID], [Employee Name] FROM [tbl_Employee]
GO
--OUTPUT

Step 2 :
Open an excel sheet and copy the data from SQL Server and paste it in excel and view the result. This step is just to demonstrate the problem.

Can you observe that leading zeros has been removed and only numbers are left ?  Lets fix it.

Step 3 :
Open a new excel sheet and right click on the column where you want to paste (place) leading zeros column and select format cell.

It will open ‘Format Cells’ window. Select Text in it and Press OK to close it.

Step 4 :
Now, copy the data from SQL Server and paste it in the excel.

Let me know if you know any better solution.