Transfer data from excel to SQL Server table

Question

I have some data in excel spreadsheet in below format:

and I want to transfer this to a SQL server table in below format:

Can you please help me out. Thank you,

Answer 1

You were not clear if this is a one-time import or part of a repetitive process. So I will tackle the transformation step. You can use CROSS APPLY to UNPIVOT your newly imported working table. Please consider:

CREATE TABLE #tmp(ID int, col_1 int, col_2 int)

INSERT INTO #tmp
VALUES(10,111,555),
(11,333,777)


select t1.id,x.col,x.val 
from #tmp t1
cross apply(VALUES(1,t1.col_1),(2,t1.col_2)) x(col,val)