Actually i have google calendar file ics, when import it , it comes in one column everything so the whole script i did to have the previouse table is the below, also the first row and second must be in same line and third, fourth must be in same line and so on :

Delete From User1

create table #Temp (Calendar_Data nvarchar(1000))

BULK INSERT #Temp FROM 'D:\basic.ics'

ALTER Table #Temp

ADD name nvarchar(500),

Date_Start date,

Date_End date,

Time_Start varchar(20),

Time_End varchar(20)

Update #Temp

Set name = (Select SUBSTRING(a.Calendar_Data, CHARINDEX(':', a.Calendar_Data) + 1, LEN(a.Calendar_Data)) As 'Name' from #Temp a where a.Calendar_Data like 'X-WR-CALNAME%')

,Date_Start = (Select SUBSTRING(b.Calendar_Data, CHARINDEX(':', b.Calendar_Data) + 1, 8) from #Temp b where b.Calendar_Data like 'DTSTART%' AND #Temp.Calendar_Data = b.Calendar_Data )

, Date_End = (Select SUBSTRING(c.Calendar_Data, CHARINDEX(':', c.Calendar_Data) + 1, 8) from #Temp C where c.Calendar_Data like 'DTEND%' AND #Temp.Calendar_Data = C.Calendar_Data )

, Time_Start = (Select SUBSTRING(d.Calendar_Data, CHARINDEX(':', d.Calendar_Data) + 10, 6) from #Temp d where d.Calendar_Data like 'DTSTART%' AND #Temp.Calendar_Data = d.Calendar_Data )

, Time_End = (Select SUBSTRING(e.Calendar_Data, CHARINDEX(':', e.Calendar_Data) + 10, 6) from #Temp e where e.Calendar_Data like 'DTEND%' AND #Temp.Calendar_Data = e.Calendar_Data )

Delete from #Temp where Date_Start is null and Date_End is null and Time_Start is null and Time_End is null

-- Insert into User table

Insert into User1 (Name,Date_Start,Date_End,Time_Start,Time_End)

Select name,Date_Start,Date_End,

(select cast(msdb.dbo.agent_datetime('19000101', Time_Start) as time(0)))

,(select cast(msdb.dbo.agent_datetime('19000101', Time_End) as time(0)))

from #Temp

Drop table #Temp