excel data pivot/unpivot to sql server 2005 table

The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?

data:

Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...

sql server table

create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)

DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...

First import the data into temp table..

Then use the UNPIVOT operator to get the required data..

Here the complete query,

Code Snippet

/*

create table tblData

(

DataID int identity(1,1),

Ref int,

Sector varchar(20),

Name varchar(20),

Date datetime,

value decimal(6,2)

)

*/

Code Snippet

Create Table #tempdata (

[Ref] int ,

[Sector] Varchar(100) ,

[Name] Varchar(100) ,

[28-Dec-2006] float ,

[29-Dec-2006] float

);

Insert Into #tempdata Values('1','Sovereign','RUSSIA05',NULL,'173.21');

Insert Into #tempdata Values('2','Sovereign','RUSSIA07','102.99','102.22');

Insert Into #tempdata Values('3','Sovereign','RUSSIA10','114.33','104.63');

Insert Into #tempdata Values('4','Sovereign','RUSSIA18','115.50','145.50');

Go

Code Snippet

Declare @.UnPivotColumns as varchar(max)

Select@.UnPivotColumns = ''

Select @.UnPivotColumns = @.UnPivotColumns + ',[' + name + ']' from tempdb.Sys.columns

Where object_id = object_id('tempdb..#tempdata')

and column_id > 3

Select@.UnPivotColumns = Substring(@.UnPivotColumns, 2, len(@.UnPivotColumns)-1)

Insert Into tblData(ref,sector,name,date,value)

Exec ('Select ref,sector,Name,cast(date as datetime),[value]

from #tempdata unpivot([value] for [date]

in (' + @.UnPivotColumns + ') )as uptv')

Drop table #tempdata;

--To fill the missed values when the value is null

Insert Into tblData

select

fulldata.*,

tbl.value

from

(

select * from

(select distinct ref,sector,namefrom tblData) data

cross join (select distinctDatefrom tblData) dates

) fulldata

left outer join tblData tbl

on tbl.ref = fulldata.ref

and tbl.sector = fulldata.sector

and tbl.name = fulldata.name

and tbl.Date = fulldata.date

where

tbl.Date is null

Select * from tblData