Spatial SQL -> MS-SQL 2008. Create polyline spatial object

After simple points time came to create polylines.
/*
create table with geography column and spatial index to store street lines
*/


CREATE TABLE [dbo].[milano_str](
[id] [int] NOT NULL,
[name] [nvarchar](100) NULL,
[type] [nvarchar](50) NULL,
[lat] FLOAT NULL,
[lon] FLOAT NULL,
[g] GEOGRAPHY
CONSTRAINT [PK_milano_str] PRIMARY KEY CLUSTERED
([id] ASC))
ON [PRIMARY];

CREATE SPATIAL INDEX [geo_sidx] ON [dbo].[milano_str]
([g]) USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

/*
add SRID constraint to ensure use of "WGS 84" spatial reference system
*/


ALTER TABLE [dbo].[milano_str] WITH CHECK
ADD CONSTRAINT [enforce_srid_geography_milano_str]
CHECK ([g].[STSrid]=4326);

ALTER TABLE [dbo].[milano_str] CHECK CONSTRAINT [enforce_srid_geography_milano_str];



Following select from milano street source data:
select lat + ' ' + lon + ', ' from dbo.milan_street_src
where street_id = 3
order by street_id, node_no;

Returns coordinates of line vertices. Line represents aproximate path of Via Dante.
(No column name)
9.182470116072778 45.46802940960921,
9.183925755833585 45.46695843148468,
9.185194564498829 45.46592556185459,
9.186244976887451 45.465157677802,
9.18723835284689 45.46453857917489,
9.188393029018231 45.46380327025498,


Create polyline from string. Copy-Paste from prev results:
INSERT INTO milano_str ([id],[name],[type],[g])
SELECT street_id, name, [type],
'LINESTRING (
9.182470116072778 45.46802940960921,
9.183925755833585 45.46695843148468,
9.185194564498829 45.46592556185459,
9.186244976887451 45.465157677802,
9.18723835284689 45.46453857917489,
9.188393029018231 45.46380327025498
)'

FROM dbo.milan_street_src
WHERE street_id=3
GROUP BY userid, [name],[type];



sqlexamples.info